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

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

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