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<=T ODAY())"
.FormatConditions(1).Font.Bold = True
.FormatConditions(1).Font.ColorIndex = 3
End With
Next

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

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" wrote in message
...
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<=T ODAY())"
.FormatConditions(1).Font.Bold = True
.FormatConditions(1).Font.ColorIndex = 3
End With
Next

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

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
...
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" wrote in message
...
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<=T ODAY())"
.FormatConditions(1).Font.Bold = True
.FormatConditions(1).Font.ColorIndex = 3
End With
Next

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

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" wrote in message
...
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
...
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" wrote in message
...
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<=T ODAY())"
.FormatConditions(1).Font.Bold = True
.FormatConditions(1).Font.ColorIndex = 3
End With
Next

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

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" wrote in message
...
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" wrote in message
...
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
...
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" wrote in message
...
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<=T ODAY())"
.FormatConditions(1).Font.Bold = True
.FormatConditions(1).Font.ColorIndex = 3
End With
Next

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

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
...
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" wrote in message
...
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" wrote in message
...
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
...
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" wrote in message
...
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<=T ODAY())"
.FormatConditions(1).Font.Bold = True
.FormatConditions(1).Font.ColorIndex = 3
End With
Next

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

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" wrote in message
...
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
...
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" wrote in message
...
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" wrote in message
...
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
...
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" wrote in message
...
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<=T ODAY())"
.FormatConditions(1).Font.Bold = True
.FormatConditions(1).Font.ColorIndex = 3
End With
Next

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

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
...
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" wrote in message
...
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
...
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" wrote in message
...
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" wrote in message
...
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
...
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" wrote in message
...
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<=T ODAY())"
.FormatConditions(1).Font.Bold = True
.FormatConditions(1).Font.ColorIndex = 3
End With
Next