Applying Conditional Formatting in VBA

[PipBoy808]

I have the following conditional formatting formula:

=RIGHT($A12,3)="day"

which I apply to the range $A$12:$N$300 by applying a black fill.

Essentially, it identifies headers (e.g. Monday, Tuesday) in column A and then colours the entire row black to keep different sections of data apart. Manually, it works perfectly.

However, whenever I run a macro that imports new data, the formula shifts as well. The conditional formatting is never quite perfect.

What I'd like to do is incorporate this conditional formatting into the VBA itself so that it becomes a hard-coded part of the data import. Is there any way to do this?

Thanks very much!

 

[Abhijeet R. Joshi]

Hi Pipboy..

Try using the below code

Sub CF_PipBoy()
Dim DRC, i
 
DRC = Range("A" & Application.Rows.Count).End(xlUp).Row
For i = 1 To DRC 'change the rows to 2 if Col.A has headers
    If Right(Cells(i, 1).Value, 3) = "day" Then
    Else
    Rows(i).Interior.Color = vbBlack
    End If
Next i
MsgBox "Done"
Beep:
End Sub