Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment value inside conditional format
I'm using Excel 97.
I'm trying to apply conditional formatting to a range of cells. I will also need to increment (by 1) the named range inside of the formula in the conditional format. The code below has no problem applying the conditional format to the entire range, the part I'm stumbling over is how to get it to increment. All references to contrib are named ranges or named cells in other sheets. The cells are named as follows: contrib1, contrib2, etc. ---Here's an example of what happens after the macro executes--- I go to Format / Conditional Format and find: Formula Is =LEN(contrib & n)=0 The above formula is applied to every cell in the entire range ---Here's an example of what I want to find--- Format / Conditional Format Formula Is =LEN(contrib1)=0 << inside cell(K3) Formula Is =LEN(contrib2)=0 << inside cell(K4) Formula Is =LEN(contrib3)=0 << inside cell(K5) Where contrib is incremented by 1 for each cell in the entire range. I'm very close to getting it right but I just can't figure out how to append my variable (num) to contrib. I think what I already have setup, as far as incrementing, will work fine. I just don't know how to assign a pointer to my variable inside of the parenthesis. Here's the code I'm using: -------------------------------------------------------------- Option Explicit Sub CondFormat() Dim num As Integer Dim cell As Range num = 1 For Each cell In Range("K3:D55") cell.FormatConditions.Delete cell.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=LEN(contrib & num)=0" cell.FormatConditions(1).Interior.ColorIndex = 2 num = num + 1 Next cell End Sub -------------------------------------------------------------- Thank you for any help! I appreciate it! Bill Burns |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment value inside conditional format
Sub CondFormat()
Dim num As Integer Dim cell As Range Dim iCol As Long num = 1 For iCol = 11 To 4 Step -1 For Each cell In Range(Cells(3, iCol), Cells(55, iCol)) cell.FormatConditions.Delete cell.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=LEN(contrib" & num & ")=0" cell.FormatConditions(1).Interior.ColorIndex = 2 num = num + 1 Next cell Next iCol End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Bill Burns" wrote in message ... I'm using Excel 97. I'm trying to apply conditional formatting to a range of cells. I will also need to increment (by 1) the named range inside of the formula in the conditional format. The code below has no problem applying the conditional format to the entire range, the part I'm stumbling over is how to get it to increment. All references to contrib are named ranges or named cells in other sheets. The cells are named as follows: contrib1, contrib2, etc. ---Here's an example of what happens after the macro executes--- I go to Format / Conditional Format and find: Formula Is =LEN(contrib & n)=0 The above formula is applied to every cell in the entire range ---Here's an example of what I want to find--- Format / Conditional Format Formula Is =LEN(contrib1)=0 << inside cell(K3) Formula Is =LEN(contrib2)=0 << inside cell(K4) Formula Is =LEN(contrib3)=0 << inside cell(K5) Where contrib is incremented by 1 for each cell in the entire range. I'm very close to getting it right but I just can't figure out how to append my variable (num) to contrib. I think what I already have setup, as far as incrementing, will work fine. I just don't know how to assign a pointer to my variable inside of the parenthesis. Here's the code I'm using: -------------------------------------------------------------- Option Explicit Sub CondFormat() Dim num As Integer Dim cell As Range num = 1 For Each cell In Range("K3:D55") cell.FormatConditions.Delete cell.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=LEN(contrib & num)=0" cell.FormatConditions(1).Interior.ColorIndex = 2 num = num + 1 Next cell End Sub -------------------------------------------------------------- Thank you for any help! I appreciate it! Bill Burns |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment value inside conditional format
Bob Phillips wrote:
Sub CondFormat() Dim num As Integer Dim cell As Range Dim iCol As Long num = 1 For iCol = 11 To 4 Step -1 For Each cell In Range(Cells(3, iCol), Cells(55, iCol)) cell.FormatConditions.Delete cell.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=LEN(contrib" & num & ")=0" cell.FormatConditions(1).Interior.ColorIndex = 2 num = num + 1 Next cell Next iCol End Sub Bob, Thanks so much! It works great. I gave you the wrong cell range in my initial post. The range I gave you was "Range("K3:D55") and it should have been "Range("K3:DE55")" . I modified your code (see below). Thank You! Bill Burns ----------------------Modified Code--------------------------- Option Explicit Sub CondFormat() Dim num As Integer Dim cell As Range Dim iCol As Long num = 1 For iCol = 11 To 109 For Each cell In Range(Cells(3, iCol), Cells(55, iCol)) cell.FormatConditions.Delete cell.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=LEN(contrib" & num & ")=0" cell.FormatConditions(1).Interior.ColorIndex = 2 num = num + 1 Next cell Next iCol End Sub -------------------------------------------------------------- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment value inside conditional format
"Bill Burns" wrote in message ... Bob, Thanks so much! It works great. I gave you the wrong cell range in my initial post. The range I gave you was "Range("K3:D55") and it should have been "Range("K3:DE55")" . I modified your code (see below). Thank You! LOL!. Because the range was backwards (K3:D55), that was why I introduced the double-loop. If you had given me the right one, I would have maintained the single loop, and that would have populated the cells in the order K3, L3, M3, N3, rather thatn K3, K4, K45, etc. Serendipity or what? Bob |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a conditional formula to increment #s in a test script | Excel Discussion (Misc queries) | |||
format text inside Concatenate | Excel Worksheet Functions | |||
New Conditional Format Overriding Previous Conditional Format | Excel Discussion (Misc queries) | |||
Date Format Inside of a SUMIF Statement | Excel Worksheet Functions | |||
How do I create a bulleted list text format inside cell? | Excel Discussion (Misc queries) |