conditional format - put inside a for/next loop

[John Keith]

I would like to conditional format some cells all in the same column
with the following:

Cells(31,4).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression,
Formula1:="=C31=1"
Selection.FormatConditions(1).Interior.ColorIndex = 38

But I want to put this piece of code inside a For/Next loop to change
a large number of rows. I think I know how to change the first line
but I need to know how to change formula statement so it will work
within the loop. (The fill color of column D is determined by the
value of column C cell in the same row.)

Using excel 2003/WinXP


John Keith
(e-mail address removed)

 

[JLGWhiz]

This might work"
Sub liminal()
Dim c As Range
i = 31
For Each c In Range("D31500") 'Change Range size to suit
c.FormatConditions.Add Type:=xlExpression, _
Formula1:="=C" & i & "= 1"
c.FormatConditions(1).Interior.ColorIndex = 38
i = i + 1
Next
End Sub

 

[JE McGimpsey]

One way:

Range("D31100").Select
With Selection.FormatConditions
.Delete
.Add Type:=xlExpression, Formula1:="=C31=1"
.Item(1).Interior.ColorIndex = 30
End With

 

[John Keith]

This might work"
Sub liminal()
Dim c As Range
i = 31
For Each c In Range("D31500") 'Change Range size to suit
c.FormatConditions.Add Type:=xlExpression, _
Formula1:="=C" & i & "= 1"
c.FormatConditions(1).Interior.ColorIndex = 38
i = i + 1
Next
End Sub

Your exact suggestion above did not work perfectly (see below) but the
concept of doing the string substitution did work and I think I can
adjust to meet my requirements.

What didn't work:
- format in column D was controlled by value in column F rather than C
- increment on i wasn't right, row 34 controlled row 32

I have no idea on why this behavior but then I'm still a beginner.

Thanks for teaching me the concept of the string replacement though!!!


John Keith
(e-mail address removed)

 

[John Keith]

One way:

Range("D31100").Select
With Selection.FormatConditions
.Delete
.Add Type:=xlExpression, Formula1:="=C31=1"
.Item(1).Interior.ColorIndex = 30
End With

JE,

Thanks for teaching me how to apply this format to a range.

But you missed a key point in my OP, I needed the "=C31" to change for
each row, e.g. "=C32", "=C33" and so on.

Thank you for looking at my inquiry.



John Keith
(e-mail address removed)

 

[John Keith]

I would like to conditional format some cells all in the same column
with the following:

Cells(31,4).Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression,
Formula1:="=C31=1"
Selection.FormatConditions(1).Interior.ColorIndex = 38

But I want to put this piece of code inside a For/Next loop to change
a large number of rows. I think I know how to change the first line
but I need to know how to change formula statement so it will work
within the loop. (The fill color of column D is determined by the
value of column C cell in the same row.)

Before I got the 2 responses I was thinking about my question and I
wonder if this technique would have worked:

- set the conditional format in row 31
- copy cell D31
- paste special formats into other cells in column D where I needed it

I assume the cell reference in the formula would have been updated to
correctly refer to the adjacent cell in the row.


John Keith
(e-mail address removed)

 

[JE McGimpsey]

Thanks for teaching me how to apply this format to a range.

But you missed a key point in my OP, I needed the "=C31" to change for
each row, e.g. "=C32", "=C33" and so on.

You should try a solution before you decide it doesn't work...

 

[John Keith]

You should try a solution before you decide it doesn't work...

Like I noted in another response, I'm a beginner. Sure enough it works
but I don't have a clue why the reference is correct for each row.

Thanks for pointing this out.

PS - But now you've added to my confusion.


John Keith
(e-mail address removed)

 

[JE McGimpsey]

Like I noted in another response, I'm a beginner. Sure enough it works
but I don't have a clue why the reference is correct for each row.

It works the same way as entering the formula in the XL user interface.
Entering a relative address will cause XL to enter the CF relative to
the cells in the range.

FWIW, being a beginner is among the worst of excuses for not trying
something. Trying, and attempting to figure out why it works, is one of
the best ways to quickly get beyond beginner status!