Entire row highlight

afeldsh

New Member
Joined
Apr 24, 2014
Messages
37
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
 
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
 
Upvote 0
If I correctly understood, could be:
VBA Code:
If UCase(c.Value) = "EXPIRED" Or Cells(c.Row, "xx").Value <= (Date - 30) Then '<- adjust column xx as needed and 30 as deadline
 
Upvote 0
Solution

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top