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

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

"John Keith" wrote:

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

One way:

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



In article <(E-Mail Removed)>,
John Keith <(E-Mail Removed)> wrote:

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

On Thu, 27 Nov 2008 19:53:01 -0800, JLGWhiz
<(E-Mail Removed)> wrote:

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

On Thu, 27 Nov 2008 21:08:27 -0700, JE McGimpsey
<(E-Mail Removed)> wrote:

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

On Thu, 27 Nov 2008 20:11:08 -0700, John Keith <(E-Mail Removed)> wrote:

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

In article <(E-Mail Removed)>,
John Keith <(E-Mail Removed)> wrote:

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

On Fri, 28 Nov 2008 04:10:02 -0700, JE McGimpsey
<(E-Mail Removed)> wrote:

>In article <(E-Mail Removed)>,
> John Keith <(E-Mail Removed)> wrote:
>
>> 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...

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

In article <(E-Mail Removed)>,
John Keith <(E-Mail Removed)> wrote:

> On Fri, 28 Nov 2008 04:10:02 -0700, JE McGimpsey
> <(E-Mail Removed)> wrote:
>
> >In article <(E-Mail Removed)>,
> > John Keith <(E-Mail Removed)> wrote:
> >
> >> 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...
>
> 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!