| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Conditonal Format Formula
Need help writing a formula that does this
If IsEmpty(H3) And CurrentCell(G3) <= Today() The Conditional Format goes in Cell G3. It should check the adjacent cell H3 for data, if data exists do nothing, if H3 is Empty AND G3 is Less than or Equal to today, then trip the format. I'd also like to utilize this code in VBA. Below is a snippet of my code that applies my current conditional formatting. How would you write the proper syntax for the statement above so that, every cell in column G is checking it's adjecent H cell versus all referencing H3? For Each Sh In ThisWorkbook.Worksheets shLast = LastRow(Sh) With Sh.Range("G2:G" & shLast) .FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, Formula1:="=TODAY()" .FormatConditions(1).Font.Bold = True .FormatConditions(1).Font.ColorIndex = 3 End With Next |
|
#2
|
|||
|
|||
|
YB79,
You simply write the format conditions formula for the first cell of the range, and Excel updates it properly. With Sh.Range("G2:G" & shLast) .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, _ Formula1:="=AND(H2<"""",G2<TODAY())" .FormatConditions(1).Font.Bold = True .FormatConditions(1).Interior.ColorIndex = 3 End With HTH, Bernie MS Excel MVP "Yogi_Bear_79" wrote in message ... Need help writing a formula that does this If IsEmpty(H3) And CurrentCell(G3) <= Today() The Conditional Format goes in Cell G3. It should check the adjacent cell H3 for data, if data exists do nothing, if H3 is Empty AND G3 is Less than or Equal to today, then trip the format. I'd also like to utilize this code in VBA. Below is a snippet of my code that applies my current conditional formatting. How would you write the proper syntax for the statement above so that, every cell in column G is checking it's adjecent H cell versus all referencing H3? For Each Sh In ThisWorkbook.Worksheets shLast = LastRow(Sh) With Sh.Range("G2:G" & shLast) .FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, Formula1:="=TODAY()" .FormatConditions(1).Font.Bold = True .FormatConditions(1).Font.ColorIndex = 3 End With Next |
|
#3
|
|||
|
|||
|
Bernie,
Ok I had to adjust the code a bit. I pasted it below. I am having very unusaul problems with it. First I had to replace < with =. Then I had to place a $ in front of the column letters because it was changing them. Now it works fairly close except on some sheets it starts at H1 & G1 versus H2 & G2. On other sheets it creates a !REF error in the formula. What I expect to happen is on every sheet starting at Cell G2 it adds the following conditonal format, it should Autifill down the column incrementing as it goes. But on some sheets it starts at H1 & G1 which is not what I instructed it to do 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" <deitbe @ consumer dot org wrote in message ... YB79, You simply write the format conditions formula for the first cell of the range, and Excel updates it properly. With Sh.Range("G2:G" & shLast) .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, _ Formula1:="=AND(H2<"""",G2<TODAY())" .FormatConditions(1).Font.Bold = True .FormatConditions(1).Interior.ColorIndex = 3 End With HTH, Bernie MS Excel MVP "Yogi_Bear_79" wrote in message ... Need help writing a formula that does this If IsEmpty(H3) And CurrentCell(G3) <= Today() The Conditional Format goes in Cell G3. It should check the adjacent cell H3 for data, if data exists do nothing, if H3 is Empty AND G3 is Less than or Equal to today, then trip the format. I'd also like to utilize this code in VBA. Below is a snippet of my code that applies my current conditional formatting. How would you write the proper syntax for the statement above so that, every cell in column G is checking it's adjecent H cell versus all referencing H3? For Each Sh In ThisWorkbook.Worksheets shLast = LastRow(Sh) With Sh.Range("G2:G" & shLast) .FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, Formula1:="=TODAY()" .FormatConditions(1).Font.Bold = True .FormatConditions(1).Font.ColorIndex = 3 End With Next |
|
#4
|
|||
|
|||
|
What is the code for your function LastRow?
shLast = LastRow(Sh) -- HTH, Bernie MS Excel MVP "Yogi_Bear_79" wrote in message ... Bernie, Ok I had to adjust the code a bit. I pasted it below. I am having very unusaul problems with it. First I had to replace < with =. Then I had to place a $ in front of the column letters because it was changing them. Now it works fairly close except on some sheets it starts at H1 & G1 versus H2 & G2. On other sheets it creates a !REF error in the formula. What I expect to happen is on every sheet starting at Cell G2 it adds the following conditonal format, it should Autifill down the column incrementing as it goes. But on some sheets it starts at H1 & G1 which is not what I instructed it to do 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" <deitbe @ consumer dot org wrote in message ... YB79, You simply write the format conditions formula for the first cell of the range, and Excel updates it properly. With Sh.Range("G2:G" & shLast) .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, _ Formula1:="=AND(H2<"""",G2<TODAY())" .FormatConditions(1).Font.Bold = True .FormatConditions(1).Interior.ColorIndex = 3 End With HTH, Bernie MS Excel MVP "Yogi_Bear_79" wrote in message ... Need help writing a formula that does this If IsEmpty(H3) And CurrentCell(G3) <= Today() The Conditional Format goes in Cell G3. It should check the adjacent cell H3 for data, if data exists do nothing, if H3 is Empty AND G3 is Less than or Equal to today, then trip the format. I'd also like to utilize this code in VBA. Below is a snippet of my code that applies my current conditional formatting. How would you write the proper syntax for the statement above so that, every cell in column G is checking it's adjecent H cell versus all referencing H3? For Each Sh In ThisWorkbook.Worksheets shLast = LastRow(Sh) With Sh.Range("G2:G" & shLast) .FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, Formula1:="=TODAY()" .FormatConditions(1).Font.Bold = True .FormatConditions(1).Font.ColorIndex = 3 End With Next |
|
#5
|
|||
|
|||
|
Function LastRow(Sh As Worksheet)
'Courtesy of www.contextures.com 'Purpose: ' Find the last used cell in the last used row On Error GoTo Err_Handler LastRow = Sh.Cells.Find(What:="*", _ After:=Sh.Range("A1"), _ lookat:=xlPart, _ LookIn:=xlFormulas, _ searchorder:=xlByRows, _ searchdirection:=xlPrevious, _ MatchCase:=False).Row Exit_Routine: Exit Function Err_Handler: Resume Next End Function "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... What is the code for your function LastRow? shLast = LastRow(Sh) -- HTH, Bernie MS Excel MVP "Yogi_Bear_79" wrote in message ... Bernie, Ok I had to adjust the code a bit. I pasted it below. I am having very unusaul problems with it. First I had to replace < with =. Then I had to place a $ in front of the column letters because it was changing them. Now it works fairly close except on some sheets it starts at H1 & G1 versus H2 & G2. On other sheets it creates a !REF error in the formula. What I expect to happen is on every sheet starting at Cell G2 it adds the following conditonal format, it should Autifill down the column incrementing as it goes. But on some sheets it starts at H1 & G1 which is not what I instructed it to do 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" <deitbe @ consumer dot org wrote in message ... YB79, You simply write the format conditions formula for the first cell of the range, and Excel updates it properly. With Sh.Range("G2:G" & shLast) .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, _ Formula1:="=AND(H2<"""",G2<TODAY())" .FormatConditions(1).Font.Bold = True .FormatConditions(1).Interior.ColorIndex = 3 End With HTH, Bernie MS Excel MVP "Yogi_Bear_79" wrote in message ... Need help writing a formula that does this If IsEmpty(H3) And CurrentCell(G3) <= Today() The Conditional Format goes in Cell G3. It should check the adjacent cell H3 for data, if data exists do nothing, if H3 is Empty AND G3 is Less than or Equal to today, then trip the format. I'd also like to utilize this code in VBA. Below is a snippet of my code that applies my current conditional formatting. How would you write the proper syntax for the statement above so that, every cell in column G is checking it's adjecent H cell versus all referencing H3? For Each Sh In ThisWorkbook.Worksheets shLast = LastRow(Sh) With Sh.Range("G2:G" & shLast) .FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, Formula1:="=TODAY()" .FormatConditions(1).Font.Bold = True .FormatConditions(1).Font.ColorIndex = 3 End With Next |
|
#6
|
|||
|
|||
|
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 "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... What is the code for your function LastRow? shLast = LastRow(Sh) -- HTH, Bernie MS Excel MVP "Yogi_Bear_79" wrote in message ... Bernie, Ok I had to adjust the code a bit. I pasted it below. I am having very unusaul problems with it. First I had to replace < with =. Then I had to place a $ in front of the column letters because it was changing them. Now it works fairly close except on some sheets it starts at H1 & G1 versus H2 & G2. On other sheets it creates a !REF error in the formula. What I expect to happen is on every sheet starting at Cell G2 it adds the following conditonal format, it should Autifill down the column incrementing as it goes. But on some sheets it starts at H1 & G1 which is not what I instructed it to do 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" <deitbe @ consumer dot org wrote in message ... YB79, You simply write the format conditions formula for the first cell of the range, and Excel updates it properly. With Sh.Range("G2:G" & shLast) .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, _ Formula1:="=AND(H2<"""",G2<TODAY())" .FormatConditions(1).Font.Bold = True .FormatConditions(1).Interior.ColorIndex = 3 End With HTH, Bernie MS Excel MVP "Yogi_Bear_79" wrote in message ... Need help writing a formula that does this If IsEmpty(H3) And CurrentCell(G3) <= Today() The Conditional Format goes in Cell G3. It should check the adjacent cell H3 for data, if data exists do nothing, if H3 is Empty AND G3 is Less than or Equal to today, then trip the format. I'd also like to utilize this code in VBA. Below is a snippet of my code that applies my current conditional formatting. How would you write the proper syntax for the statement above so that, every cell in column G is checking it's adjecent H cell versus all referencing H3? For Each Sh In ThisWorkbook.Worksheets shLast = LastRow(Sh) With Sh.Range("G2:G" & shLast) .FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, Formula1:="=TODAY()" .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 | |||
| Can Excel represent formula in textural format with values substi. | Excel Worksheet Functions | |||
| How do I copy a conditional format that uses a formula in a serie. | Setting up and Configuration of Excel | |||
| date format within a cell containing a formula | Excel Discussion (Misc queries) | |||
| Help with macro formula and variable | Excel Worksheet Functions | |||
| How do I format a character from a result of formula in excel? | Excel Discussion (Misc queries) | |||
Linear Mode
