Can't get CF to work properly, Active Cell problem?

[Yogi_Bear_79]

I apologize for the re-post, but I was suprised my original went unanwsered.
I'm sure someone knows what I am doing worng.

I have the following code called by the Workbook_SheetCalculate event. It
works, however it seems to have a mind of it's own. When I check the
conditonal format of Cell G2 it reads =AND($H3="",$G3<=TODAY()). It should
read H2 & G2. I don't understand what is causing this, At other times the
numbers are way
off for instance H62000 and other times I get the !REF error.

I have tried various methods to first select or activate cell G2 prior to
running this code but nothing seems to help.

What am I doing wrong that Excel won't start at G2 and autofill down
incrementing as needed

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]

YB79,

Try this version, below.

HTH,
Bernie
MS Excel MVP

Sub TryNow()
Dim sh As Worksheet
Dim shLast As Long
Dim mySel As Range
For Each sh In ThisWorkbook.Worksheets
sh.Activate
Set mySel = Selection
shLast = LastRow(sh)
With sh.Range("G2:G" & shLast)
.Select
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=AND($H2="""",$G2<=TODAY())"
.FormatConditions(1).Font.Bold = True
.FormatConditions(1).Font.ColorIndex = 3
End With
mySel.Select
Next
End Sub

 

[Yogi_Bear_79]

Bernie,

That seems to have done the trick.

thanks so much, this has been driving me crazy for days

 

[Tom Ogilvy]

but I was suprised my original went
You said you had tried the solution which now seems successful - guess it
didn't disuade Bernie.

 

[Bernie Deitrick]

Tom,

Actually, I had answered YB's original thread, and he said he would continue
it on Monday, but started a new thread, which I never saw (mostly because I
wasn't looking!). A lesson in threading - stay in the original.

HTH,
Bernie
MS Excel MVP

 

[Yogi_Bear_79]

Bernie, Tom,

I did try to stay in the original thread, but since it went unanwsered I
re-posted yesterday. I didn't mind re-posting since it was a change to the
original question. Bernie answered the orignal question, but the solution
did cause a new problem.

I thank you both, I did spend a lot of time searching google and web sites
to see what I was doing wrong. I try to be considerate before posting

 

[Bernie Deitrick]

Yogi_Bear_79,

Your last response to me in the worksheet.functions group was

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

You were going to post the LastRow code, which I didn't see until this
thread.

But I use MS OExpress, which has a nastly habit of not picking up all the
posts, so that may have been the cause of my not seeing your code.

HTH,
Bernie
MS Excel MVP

 

[Yogi_Bear_79]

Yeah, cause I actually posted the last row code on that thread prior to
posting about picking it up on monday