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

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

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
--------------------------------------------------------------

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