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

  • Thread starter Thread starter Yogi_Bear_79
  • Start date Start date
Y

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
 
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
 
Bernie,

That seems to have done the trick.

thanks so much, this has been driving me crazy for days
 
but I was suprised my original went
You said you had tried the solution which now seems successful - guess it
didn't disuade Bernie.
 
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
 
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
 
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
 
Yeah, cause I actually posted the last row code on that thread prior to
posting about picking it up on monday
 
Back
Top