Excel VBA - conditional format 1

[MemphisVBA]

I have a spreadsheet that has several columns - every time this report is ran, the amount of rows will vary. Can you help me w/ the VBA that would do this:
let's say column A has dates in it. The first date will always start at A2 (first row is a header row).
If the date at A3 is the same as A2, then I want the text in each cell to be red. Basically, if a cell's date is the same as the cell above it, make them red. I would like this to step through all the rows. I guess I could do this with conditional formatting, but I would like to learn how to do this via VBA and a for/next loop.
Thanks!

 

[PHV]

A starting point:
Dim r As Long
Range("A:A").Font.ColorIndex = xlColorIndexAutomatic
r = 3
While Cells(r, "A") <> ""
If Cells(r, "A") = Cells(r - 1, "A") Then
Cells(r, "A").Font.ColorIndex = 3
End If
r = r + 1
Wend

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MemphisVBA]

Thanks for the quick reply - I have a really stupid question: How do I initiate the code? I am more familiar with using VBA in Access... Excel programing is fairly new to me. Can you recommend a FAQ to get me started? Should I save this as a Marco? Sorry for such newbie questions.

 

[PHV]

Create a standard code module and define a procedure called, say, myFirstXLsub:

Sub myFirstXLsub()
Dim r As Long
Range("A:A").Font.ColorIndex = xlColorIndexAutomatic
r = 3
While Cells(r, "A") <> ""
  If Cells(r, "A") = Cells(r - 1, "A") Then
    Cells(r, "A").Font.ColorIndex = 3
  End If
  r = r + 1
Wend
End Sub

Then press the F5 key

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MemphisVBA]

PVH - thank you very much.

is a "Standard Code Module" the same as when I (in the VB editor) select "Insert" - "Module" on the toolbar?

If so, I have done something wrong, as when I press F5, the "go to" dialog opens and it is blank... Also, I did get this to work exactly as I needed by saving and executing the code as a macro, but I get the "Macro Security" warning when I first open the worksheet.

If this report will be sent out to the field, I am concerned that the recipients will freak out and not open it - can I work around this by saving the code in my personal workbook?

I am sorry to be asking so many questions - I tried to find out on my own first, but couldn't find anything relevant in the FAQ's...

either way, I sincerely appreciate your help.