Conditonal Format Formula

[Yogi_Bear_79]

Need help writing a formula that does this

If IsEmpty(H3) And CurrentCell(G3) <= Today()

The Conditional Format goes in Cell G3. It should check the adjacent cell
H3 for data, if data exists do nothing, if H3 is Empty AND G3 is Less than
or Equal to today, then trip the format.

I'd also like to utilize this code in VBA. Below is a snippet of my code
that applies my current conditional formatting. How would you write the
proper syntax for the statement above so that, every cell in column G is
checking it's adjecent H cell versus all referencing H3?

For Each Sh In ThisWorkbook.Worksheets
shLast = LastRow(Sh)
With Sh.Range("G2:G" & shLast)
.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual,
Formula1:="=TODAY()"
.FormatConditions(1).Font.Bold = True
.FormatConditions(1).Font.ColorIndex = 3
End With
Next

 

[Bernie Deitrick]

YB79,

You simply write the format conditions formula for the first cell of the
range, and Excel updates it properly.

With Sh.Range("G2:G" & shLast)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=AND(H2<>"""",G2<TODAY())"
.FormatConditions(1).Font.Bold = True
.FormatConditions(1).Interior.ColorIndex = 3
End With

HTH,
Bernie
MS Excel MVP

 

[Yogi_Bear_79]

Bernie,

Ok I had to adjust the code a bit. I pasted it below. I am having very
unusaul problems with it. First I had to replace <> with =. Then I had to
place a $ in front of the column letters because it was changing them. Now
it works fairly close except on some sheets it starts at H1 & G1 versus H2 &
G2. On other sheets it creates a !REF error in the formula.

What I expect to happen is on every sheet starting at Cell G2 it adds the
following conditonal format, it should Autifill down the column incrementing
as it goes. But on some sheets it starts at H1 & G1 which is not what I
instructed it to do

For Each Sh In ThisWorkbook.Worksheets
shLast = LastRow(Sh)
With Sh.Range("G2:G" & shLast)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression,
Formula1:="=AND($H2="""",$G2<=TODAY())"
.FormatConditions(1).Font.Bold = True
.FormatConditions(1).Font.ColorIndex = 3
End With
Next

 

[Bernie Deitrick]

What is the code for your function LastRow?

shLast = LastRow(Sh)

 

[Yogi_Bear_79]

Function LastRow(Sh As Worksheet)
'Courtesy of www.contextures.com
'Purpose:
' Find the last used cell in the last used row
On Error GoTo Err_Handler

LastRow = Sh.Cells.Find(What:="*", _
After:=Sh.Range("A1"), _
lookat:=xlPart, _
LookIn:=xlFormulas, _
searchorder:=xlByRows, _
searchdirection:=xlPrevious, _
MatchCase:=False).Row
Exit_Routine:
Exit Function

Err_Handler:
Resume Next

End Function

 

[Yogi_Bear_79]

Bernie,

I am afraid I will have to pick this up on Monday. I will look for you
latest repsone then , and keep this thread going