| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| OR(EXACT(Cell; Range)) does not work properly | Excel Worksheet Functions | |||
| Cell Referal between tabs fails to work properly on home computer | Excel Worksheet Functions | |||
| AutomaticScale doesn't work properly | Charts and Charting in Excel | |||
| Excel VBA-use variable in active cell formula problem | Excel Programming | |||
| Problem with Active Cell Refence | Excel Programming | |||
Linear Mode
