Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Perform action if value in cell - conditional VB stmt
Hello,
First, thank everyone for your help to me previously. I've run into a need repeatedly that I'm sure a VB code will solve. I have a spreadsheet that contains columns A to J and rows 1 to 100. The word "Paid" appears sometimes, not always, in column H of my data. I need a code to repeatedly look for every occurrance of "Paid" in column H and do the following til the end: a) highlight the *row* in my data (not the entire row in Excel beyond my data) that contains the word "Paid" in yellow (I don't want to use conditional formatting in this instance if it applies) b) insert 2 rows underneath the row that contains "Paid" & highlight those rows gray Thank you very much for your help. Studebaker |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Perform action if value in cell - conditional VB stmt
this should help...
Sub test() Dim c As Range d = WorksheetFunction.CountIf(Range("H:H"), "Paid") Range("H:H").Cells.SpecialCells(xlCellTypeConstant s).Select Clear = d * 2 + Selection.Count For Each c In Selection If c.Value = "Paid" Then c.EntireRow.Interior.ColorIndex = 36 Else c.EntireRow.Interior.ColorIndex = 0 If c.Value = "Paid" Then Range(c.Offset(1, 0), Cells(c.Row + 2, c.Column)).Insert shift:=xlDown Next c Range("H:H").Cells.SpecialCells(xlCellTypeBlanks). EntireRow.Interior.ColorIndex = 15 Range(Cells(Clear + 1, 8), Cells(65536, 8)).EntireRow.Interior.ColorIndex = 0 End Sub On Feb 29, 12:51*am, Studebaker wrote: Hello, First, thank everyone for your help to me previously. I've run into a need repeatedly that I'm sure a VB code will solve. I have a spreadsheet that contains columns A to J and rows 1 to 100. The word "Paid" appears sometimes, not always, in column H of my data. *I need a code to repeatedly look for every occurrance of "Paid" in column H and do the following til the end: a) highlight the *row* in my data (not the entire row in Excel beyond my data) that contains the word "Paid" in yellow (I don't want to use conditional formatting in this instance if it applies) b) insert 2 rows underneath the row that contains "Paid" & highlight those rows gray Thank you very much for your help. Studebaker |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Perform action if value in cell - conditional VB stmt
Hello,
Thank you for this. I tried to run it but I got a "Compile error: Syntax error" message. The debugger highlights & has in red font this part of the code: If c.Value = "Yes" Then Range(c.Offset(1, 0), Cells(c.Row + 2, c.Column)).Insert shift:=xlDown (all in red font) Next c (this is not in red font so I assume this is not the problem) Range("F:F").Cells.SpecialCells(xlCellTypeBlanks). EntireRow.Interior.ColorIndex (this is not in red font so I assume this is not the problem) = 15 (the "=15" is in red font) Range(Cells(Clear + 1, 8), Cells(65536, 8)).EntireRow.Interior.ColorIndex = 0 (all in red font) Can you help? Thanks very much. " wrote: this should help... Sub test() Dim c As Range d = WorksheetFunction.CountIf(Range("H:H"), "Paid") Range("H:H").Cells.SpecialCells(xlCellTypeConstant s).Select Clear = d * 2 + Selection.Count For Each c In Selection If c.Value = "Paid" Then c.EntireRow.Interior.ColorIndex = 36 Else c.EntireRow.Interior.ColorIndex = 0 If c.Value = "Paid" Then Range(c.Offset(1, 0), Cells(c.Row + 2, c.Column)).Insert shift:=xlDown Next c Range("H:H").Cells.SpecialCells(xlCellTypeBlanks). EntireRow.Interior.ColorIndex = 15 Range(Cells(Clear + 1, 8), Cells(65536, 8)).EntireRow.Interior.ColorIndex = 0 End Sub On Feb 29, 12:51 am, Studebaker wrote: Hello, First, thank everyone for your help to me previously. I've run into a need repeatedly that I'm sure a VB code will solve. I have a spreadsheet that contains columns A to J and rows 1 to 100. The word "Paid" appears sometimes, not always, in column H of my data. I need a code to repeatedly look for every occurrance of "Paid" in column H and do the following til the end: a) highlight the *row* in my data (not the entire row in Excel beyond my data) that contains the word "Paid" in yellow (I don't want to use conditional formatting in this instance if it applies) b) insert 2 rows underneath the row that contains "Paid" & highlight those rows gray Thank you very much for your help. Studebaker |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
%1 appears in "Application used to perform action" when trying to edit a file type | Excel Worksheet Functions | |||
Perform a "Conditional Substitute/Replace" | Excel Worksheet Functions | |||
If stmt and assigning a value to another cell | Excel Worksheet Functions | |||
How do I perform a contains function for a specific cell? | Excel Worksheet Functions | |||
can you perform a character count per cell with numbers? | Excel Discussion (Misc queries) |