Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional format cell ranges with loop?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional format cell ranges with loop?
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 -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional format cell ranges with loop?
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 -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional format cell ranges with loop?
Ok. Just remove " Range. " from all such instances i.e. wherever you
have range.cells just keep cells, and try again - Manges -- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional format cell ranges with loop?
That did the trick in terms of erliminating the error, but running th
macro now I see it having no discernable affect. : -- Message posted from http://www.ExcelForum.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional format cell ranges with loop?
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 -- Message posted from http://www.ExcelForum.com |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional format cell ranges with loop?
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 -- Message posted from http://www.ExcelForum.com |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional format cell ranges with loop?
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 -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Format based on date ranges | Excel Worksheet Functions | |||
Loop through ranges | Excel Programming | |||
Loop through ranges columns | Excel Programming | |||
How to loop through all ranges in a worksheet | Excel Programming | |||
loop to name ranges | Excel Programming |