Conditional format won't work for cell containing "TODAY()"

[Excelerate2]

Really simple question: I am using Excel 2010 and noticed that if I try to conditional format a cell that contains a formula "=TODAY()", that it won't highlight!
I have tried every combination but can't get it to work, even if I get it to highlight, as soon as I enter the actual date it still stays highlighted!

It's really simple: I just need one cell (I3) to highlight if it contains the formula for today's date "=TODAY()" and also if it is blank.
I am using it as a reminder for the user to enter today's date in the cell (because if they don't then the form will always show today's date and not the date created, etc)
Any help would be great, very frustrating as CF works for everything else except this simple thing.

 

[Coding4Fun]

I don't have Excel 2010 but it sounds like you are using the "Formula" option to try to apply the conditional formatting which as you mentioned does not work.

This will not work

This should work to highlight today

 

[Excelerate2]

I don't have Excel 2010 but it sounds like you are using the "Formula" option to try to apply the conditional formatting which as you mentioned does not work.

This will not work

View attachment 64133
This should work to highlight today

View attachment 64134

Yes, I have tried 'highlight cell rules' and 'cells that contain' "TODAY" etc, with no luck...

 

[lrobbo314]

Book1
	A
3	Dates
4	5/1/2022
5	5/2/2022
6	5/3/2022
7	5/4/2022
8	5/5/2022
9	5/6/2022
10	5/7/2022
11	5/8/2022
12	5/9/2022
13	5/10/2022
14	5/11/2022
15	5/12/2022
16	5/13/2022
17	5/14/2022
18	5/15/2022
19	5/16/2022
20	5/17/2022
21	5/18/2022
22	5/19/2022
23	5/20/2022
24	5/21/2022
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A4:A24	Expression	=A4=TODAY()	text	NO

 

[Excelerate2]

Thanks guys but I don't need it to find today's date, I just just need to highlight one cell (the 'Date' cell = I3), if it finds the formula =TODAY(), or if that same cell (I3) is BLANK.
The If Blank condition works fine, but it doesn't work when finding =TODAY() in that cell, actually it works (it highlights) but then once you type in the date (as we request they do to override the =TODAY(), then the cell still stays highlighted.

Anyway, maybe can write some vba for cell I3 instead?

 

[Coding4Fun]

Maybe if VBA works for you

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("I3").Formula = "=TODAY()" Or IsEmpty(Range("I3").Value) Then

    With Range("I3").Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End If


If Not IsEmpty(Range("I3").Value) And Range("I3").Formula <> "=TODAY()" Then
    With Range("I3").Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End If



End Sub

 

[Excelerate2]

Wow - Thank you, I will try that tonight!

 

[Excelerate2]

Maybe if VBA works for you

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("I3").Formula = "=TODAY()" Or IsEmpty(Range("I3").Value) Then

    With Range("I3").Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End If


If Not IsEmpty(Range("I3").Value) And Range("I3").Formula <> "=TODAY()" Then
    With Range("I3").Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End If



End Sub

Thanks again, would you happen to know how to fit this new code into my existing code for this tab?
I get the Ambiguous error due to already having Worksheet_Change, and when I try to paste it in without that I will get a different error
(Your code works perfectly on a blank tab though)

Here's my existing code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Address(False, False) = "I11" Then
If IsEmpty(.Value) Then
Application.EnableEvents = False
.Formula = "=IF(AND(C11>0,C10>0),I10*C11+C10,0)"
Application.EnableEvents = True
End If
End If

If .Address(False, False) = "H119" Then
If IsEmpty(.Value) Then
Application.EnableEvents = False
.Formula = "='" & Sheet16.Name & "'!I181"
Application.EnableEvents = True
End If
End If
End With

If Intersect(Target, Range("DropMenu")) Is Nothing Then Exit Sub 'change cell address (in red) to be protected here
If Target.Value = "" Then
MsgBox "Sorry, you cannot delete," & " Please select from the dropdown menu"
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With
End If

End Sub

 

[Coding4Fun]

Perhaps:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Range("I3").Formula = "=TODAY()" Or IsEmpty(Range("I3").Value) Then

    With Range("I3").Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End If


If Not IsEmpty(Range("I3").Value) And Range("I3").Formula <> "=TODAY()" Then
    With Range("I3").Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End If

With Target
If .Address(False, False) = "I11" Then
If IsEmpty(.Value) Then
Application.EnableEvents = False
.Formula = "=IF(AND(C11>0,C10>0),I10*C11+C10,0)"
Application.EnableEvents = True
End If
End If

If .Address(False, False) = "H119" Then
If IsEmpty(.Value) Then
Application.EnableEvents = False
.Formula = "='" & Sheet16.Name & "'!I181"
Application.EnableEvents = True
End If
End If
End With

If Intersect(Target, Range("DropMenu")) Is Nothing Then Exit Sub 'change cell address (in red) to be protected here
If Target.Value = "" Then
MsgBox "Sorry, you cannot delete," & " Please select from the dropdown menu"
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With
End If

End Sub

 

[Excelerate2]

Perhaps:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Range("I3").Formula = "=TODAY()" Or IsEmpty(Range("I3").Value) Then

    With Range("I3").Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End If


If Not IsEmpty(Range("I3").Value) And Range("I3").Formula <> "=TODAY()" Then
    With Range("I3").Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End If

With Target
If .Address(False, False) = "I11" Then
If IsEmpty(.Value) Then
Application.EnableEvents = False
.Formula = "=IF(AND(C11>0,C10>0),I10*C11+C10,0)"
Application.EnableEvents = True
End If
End If

If .Address(False, False) = "H119" Then
If IsEmpty(.Value) Then
Application.EnableEvents = False
.Formula = "='" & Sheet16.Name & "'!I181"
Application.EnableEvents = True
End If
End If
End With

If Intersect(Target, Range("DropMenu")) Is Nothing Then Exit Sub 'change cell address (in red) to be protected here
If Target.Value = "" Then
MsgBox "Sorry, you cannot delete," & " Please select from the dropdown menu"
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With
End If

End Sub

Hello, yes I tried inserting it that way but I get an error 1004 and the code will show a highlighted error at the .Pattern = xlSolid
(Thx, sorry for the trouble)