Increment value inside conditional format

B

Bill Burns

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
 
B

Bob Phillips

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

Bill Burns

Bob said:
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
Click to expand...

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

Bob Phillips

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!
Click to expand...

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
 
Top