Change row color based on other cell values within a specific range

[Q_cripple]

Hello everyone,

I've been struggling to code VBA (I'm new to VBA). I can't use conditional formatting because it's an Excel being continuously enriched with new data.

The aim is to color the font within a specific range of columns (A:S):
1. in red if the cell in column A is dated to today.
2. no coloring (black) if 1 does not apply (= the date in column A is older than today).
3. In green if the cell in column R contains a date (this takes precedence over 1 and 2).

I started working on 1 only but it's proven too big a challenge to work on specific range. The below is how far I got. This works for 1 but trying to add If for #2, I get errors (next without for, stuff like that). Can someone help out with a code?

Thanks in advance for any help you can provide.
PS: I tried more complex iterations with Dims but I just ended up getting lost and settled for this simple option, which took me 4 hours anyways...

Sub Format_cells()

Dim Cell As Range

Worksheets("Sheet1").Range("A2:S2").Select
Range(Selection, Selection.End(xlDown)).Select

For Each Cell In Selection

If Cell.Value = Worksheets("Sheet2").Range("G3") Then 'This is to get today's date from another sheet.
Cell.EntireRow.Font.ColorIndex = 3

End If

Next

End Sub

 

[Q_cripple]

Btw I tried using:

ElseIf Cell.Value < Worksheets("Sheet2").Range("G3") Then
Cell.EntireRow.Font.ColorIndex = 0

However it just overwrites the first part.