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

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


"Yogi_Bear_79" <[email protected]> wrote in message
news:[email protected]...
> 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,

That seems to have done the trick.

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


"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
>
>

> but I was suprised my original went
> > unanwsered.

You said you had tried the solution which now seems successful - guess it
didn't disuade Bernie.

--
Regards.
Tom Ogilvy


"Yogi_Bear_79" <[email protected]> wrote in message
news:[email protected]...
> Bernie,
>
> That seems to have done the trick.
>
> thanks so much, this has been driving me crazy for days
>
>
> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >
> > >
> >
> >
>
>

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


"Tom Ogilvy" <[email protected]> wrote in message
news:%[email protected]...
> > but I was suprised my original went
> > > unanwsered.
>
> You said you had tried the solution which now seems successful - guess it
> didn't disuade Bernie.
>
> --
> Regards.
> Tom Ogilvy
>
>
> "Yogi_Bear_79" <[email protected]> wrote in message
> news:[email protected]...
> > Bernie,
> >
> > That seems to have done the trick.
> >
> > thanks so much, this has been driving me crazy for days
> >
> >
> > "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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, 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" <deitbe @ consumer dot org> wrote in message
news:%[email protected]...
> 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
>
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:%[email protected]...
> > > but I was suprised my original went
> > > > unanwsered.
> >
> > You said you had tried the solution which now seems successful - guess
it
> > didn't disuade Bernie.
> >
> > --
> > Regards.
> > Tom Ogilvy
> >
> >
> > "Yogi_Bear_79" <[email protected]> wrote in message
> > news:[email protected]...
> > > Bernie,
> > >
> > > That seems to have done the trick.
> > >
> > > thanks so much, this has been driving me crazy for days
> > >
> > >
> > > "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <deitbe @ consumer dot org> wrote in message
> news:%[email protected]...
> > 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
> >
> >
> > "Tom Ogilvy" <[email protected]> wrote in message
> > news:%[email protected]...
> > > > but I was suprised my original went
> > > > > unanwsered.
> > >
> > > You said you had tried the solution which now seems successful - guess
> it
> > > didn't disuade Bernie.
> > >
> > > --
> > > Regards.
> > > Tom Ogilvy
> > >
> > >
> > > "Yogi_Bear_79" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Bernie,
> > > >
> > > > That seems to have done the trick.
> > > >
> > > > thanks so much, this has been driving me crazy for days
> > > >
> > > >
> > > > "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> > > > news:[email protected]...
> > > > > 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" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

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


"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <deitbe @ consumer dot org> wrote in message
> > news:%[email protected]...
> > > 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
> > >
> > >
> > > "Tom Ogilvy" <[email protected]> wrote in message
> > > news:%[email protected]...
> > > > > but I was suprised my original went
> > > > > > unanwsered.
> > > >
> > > > You said you had tried the solution which now seems successful -
guess
> > it
> > > > didn't disuade Bernie.
> > > >
> > > > --
> > > > Regards.
> > > > Tom Ogilvy
> > > >
> > > >
> > > > "Yogi_Bear_79" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Bernie,
> > > > >
> > > > > That seems to have done the trick.
> > > > >
> > > > > thanks so much, this has been driving me crazy for days
> > > > >
> > > > >
> > > > > "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> > > > > news:[email protected]...
> > > > > > 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" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > 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
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>