Color Date Cell in Column based on Criteria

1) all text is "greater" than all numbers, that's an Excel thing. So "a" is greater the 1,000,000. Trust me.

2) you defined a "string" variable in your macro and then put the date from your sheet into it. That converted that date into a STRING which is text, and ALL text is greater than all numbers. (see #1)

Sub ColorDate()
Dim date_lookup As Date, A As Range, dd As Range

date_lookup = Sheets("FrontEndApp").Range("date_lookup").Value

Set A = Range("L:L").SpecialCells(xlConstants)       'only the cells with values in them

For Each dd In A 
    If dd > date_lookup Then dd.Interior.ColorIndex = 3
Next dd

3) having said all that, there's no reason to do this with a macro when you can get this effect in realtime on column L by using conditional formatting.

- Highlight column L
- Apply a new conditional formatting rule with the formula:
=$L1 > Date_Lookup

No offense, but that seems like a question you could answer for yourself by Googling or simply turning on the macro recorder and seeing how it records you selecting all those columns.

Set A = Range("L:L, N:N, P:P, T:T").SpecialCells(xlConstants)       'only the cells with values in them

If that takes care of your original query, please select Thread Tools from the menu above and mark the thread as solved. Thanks.