Highlight cells based on text

[ai1094]

I have the following table in excel:

V W X Y Z
[TABLE="width: 500"]
<tbody>[TR]
[TD]Project Date[/TD]
[TD]Modeled Line Item[/TD]
[TD]Included in Line Item[/TD]
[TD]Both[/TD]
[TD]Confirm Both[/TD]
[/TR]
[TR]
[TD]Project Date[/TD]
[TD][/TD]
[TD][/TD]
[TD]Both[/TD]
[TD]Confirm Both[/TD]
[/TR]
[TR]
[TD]Project Date[/TD]
[TD]Modeled Line Item[/TD]
[TD]Included in Line Item[/TD]
[TD]Both[/TD]
[TD]Confirm Both[/TD]
[/TR]
[TR]
[TD]Project Date[/TD]
[TD]Modeled Line Item[/TD]
[TD]Excluded Line Item[/TD]
[TD]Both[/TD]
[TD]Confirm Both[/TD]
[/TR]
[TR]
[TD]Project Date[/TD]
[TD]Modeled Line Item[/TD]
[TD]Excluded Line Item[/TD]
[TD]Both[/TD]
[TD]Confirm Both[/TD]
[/TR]
[TR]
[TD]Project Date[/TD]
[TD][/TD]
[TD][/TD]
[TD]Both[/TD]
[TD]Confirm Both[/TD]
[/TR]
[TR]
[TD]Project Date[/TD]
[TD][/TD]
[TD][/TD]
[TD]Both[/TD]
[TD]Confirm Both[/TD]
[/TR]
</tbody>[/TABLE]


I need help creating a VBA code that will highlight the rows (V:Z) a light red if Column X has Excluded Line Item.
One thing to keep in mind is that Columns V:Z all have formulas in the cells that auto fills to the end of the row so I'm not sure if that will affect the code.

Any help would be great. Thanks!

 

[Fluff]

Is there any particular reason for needing VBA? This can easily be done with conditional formatting

 

[ai1094]

I have a VBA code that creates this table and a whole bunch of other stuff. I figured I could just include it in the code so I don't have to keep manually putting a conditional format

Is there any particular reason for needing VBA? This can easily be done with conditional formatting

 

[Joe4]

You can add setting up the Conditional Formatting in your VBA code.
Just turn on the Macro Recorder and record yourself performing the Conditional Formatting manually, and you will have recorded most of the VBA code you need to do that.

 

[ai1094]

The problem I am having with that is that only the cell is highlighted. I need the whole row (V:Z) to be highlighted as well.

You can add setting up the Conditional Formatting in your VBA code.
Just turn on the Macro Recorder and record yourself performing the Conditional Formatting manually, and you will have recorded most of the VBA code you need to do that.

 

[Fluff]

Another option would be something like

Sub ai1094()
   With ActiveSheet
      .Range("V1:Z1").AutoFilter 3, "Exluded line item"
      .AutoFilter.Range.Interior.Color = vbRed
      .Range("V1:Z1").Interior.ColorIndex = xlNone
      .AutoFilterMode = False
   End With
End Sub

 

[ai1094]

This didn't work. My guess is that it's looking for a specific text, but all the cells contain formulas. Could that be an issue?

Another option would be something like

Sub ai1094()
   With ActiveSheet
      .Range("V1:Z1").AutoFilter 3, "Exluded line item"
      .AutoFilter.Range.Interior.Color = vbRed
      .Range("V1:Z1").Interior.ColorIndex = xlNone
      .AutoFilterMode = False
   End With
End Sub

 

[mikerickson]

The problem I am having with that is that only the cell is highlighted. I need the whole row (V:Z) to be highlighted as well.

Press Record

Select V2:Z2000
Enter the conditional formatting formula =($X2="Excluded Line Item") (note only one $)

Press Done

 

[ai1094]

This worked. Thanks!

Press Record

Select V2:Z2000
Enter the conditional formatting formula =($X2="Excluded Line Item") (note only one $)

Press Done