Conditional format cell ranges with loop?

[welded]

I require the ability to conditionally format a rather large range o
cells from a macro and I'm thinking that a loop of some variety woul
be the ticket. However tonight was my first foray into VB so I'm no
all the sure what I'm doing yet, simple as it may be.
So here's the scenario:

PHP code
-------------------
sub macro()
Range("D13").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""x"""
Selection.FormatConditions(1).Font.ColorIndex = xlAutomatic
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF($D$13:$Q$13,""A"")=2"
Selection.FormatConditions(2).Font.ColorIndex = 46
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF($D$13:$Q$13,""A"")>=3"
Selection.FormatConditions(3).Font.ColorIndex = 3

 

[mangesh_yadav]

this should do it......


for i = 13 to RowCount ' starting from 13 as per your example

Range.Cells(i,4).Select ' this is cell D13
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue
Operator:=xlEqual, _
Formula1:="=""x"""
Selection.FormatConditions(1).Font.ColorIndex = xlAutomatic
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF(" & Range.cells(i,4).address & ":"
Range.cells(i,17).address & ",""A"")=2"
Selection.FormatConditions(2).Font.ColorIndex = 46
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF(" & Range.cells(i,4).address & ":"
Range.cells(i,17).address & ",""A"")>=3"
Selection.FormatConditions(3).Font.ColorIndex = 3


Next


- Manges

 

[welded]

Thanks for the quick reply, but I'm afraid I'm getting a compile error
"Argument not optional" and the editor is highlighting
*Range*.Cells... as the possible culprit in each instance. I have n
idea what this means. It seems like a simple enough concept, I wish
weren't so unadept. I could do it in PHP!

I'll keep plugging away, but I fear it's a fools endevour on my own.

Thanks again for any help

 

[mangesh_yadav]

Ok. Just remove " Range. " from all such instances i.e. wherever you
have range.cells just keep cells, and try again

- Manges

 

[welded]

That did the trick in terms of erliminating the error, but running th
macro now I see it having no discernable affect. :

 

[mangesh_yadav]

I tried it on my machine. it works. I guess your program marks an A re
if the count is more than 2/3. And that is being done here on my PC.

- Manges

 

[welded]

That's correct. Basically it's an attendance sheet which leaves "x"
black, then turns two "A"s one colour, then three or more turn red
This is just to flag repeat offenders. Running it on my Mac, as I said
doesn't seem to have any effect. Highlighting all formatted cells onl
shows ones I know that I've applied myself.

I think you for your help, anyway. In the meantime I've begun to ge
clever with Paste Special which helps somewhat

 

[welded]

I seem to have gotten it working by making a small edit. I simpl
changed "for i = 13 to RowCount" to "For i = 13 To 35" and as far as
can tell it worked with no adverse effects. Using Paste Special I wa
then able to span this column's formatting over a 14 column range.

I've a number of other macros to modify, but you've given me a hug
head start (it's just too bad I did it all the really slow and hard wa
first!)

Thanks again, Mangesh