Entire row highlight

[afeldsh]

Hello guys,

Need some help with the below vba.

Instead of highlighting the whole row, I would only like to only highlight certain cells in the row, such as G:V only. Also, need help with the else statement to do nothing instead of removing the conditional formatting in the entire row.

TIA


Here's the code I am working with:

Sub HighlightExp()

Dim lastrow As Long, c As Range
Dim MyRange As Range
lastrow = Cells(Cells.Rows.Count, "BS").End(xlUp).Row
Set MyRange = Range("BS1:BS" & lastrow)
For Each c In MyRange
If UCase(c.Value) = "EXPIRED" Then
c.EntireRow.Interior.Color = vbRed
Else
c.EntireRow.Interior.Color = xlNone
End If
Next

 

[rollis13]

You could use:

Range(Cells(c.Row, "G"), Cells(c.Row, "V")).Interior.Color = vbRed

 

[afeldsh]

Thanks, that worked perfectly!

 

[afeldsh]

What if I look at another cell that contains dates instead of the wording "EXPIRED". How to automatically determine that it is expired based on the date?

Dim lastrow As Long, c As Range
Dim MyRange As Range
lastrow = Cells(Cells.Rows.Count, "BS").End(xlUp).Row
Set MyRange = Range("BS1:BS" & lastrow)
For Each c In MyRange
If UCase(c.Value) = "EXPIRED" Then
Range(Cells(c.Row, "G"), Cells(c.Row, "V")).Interior.Color = vbRed
Range(Cells(c.Row, "AJ"), Cells(c.Row, "BT")).Interior.Color = vbRed
Range(Cells(c.Row, "H"), Cells(c.Row, "H")).FormulaR1C1 = "QUOTE EXPIRED"
Else
'do nothing
End If
Next

 

[rollis13]

If I correctly understood, could be:

If UCase(c.Value) = "EXPIRED" Or Cells(c.Row, "xx").Value <= (Date - 30) Then '<- adjust column xx as needed and 30 as deadline

 

[afeldsh]

That worked perfectly. Thank you again!

 

[rollis13]

Thanks for the positive feedback, glad having been of some help.