Solved Conditional Formatting of a Row

August 15, 2013 at 10:34:21
Specs: Windows XP
Excel 2010, Conditional Formatting for Table
Hi there, I'm looking to create a macro that will format a row of 12 cells in specific colors, red or green based on a value I assign them. How do I do this? If the last cell has a value of 1, it needs to be green; if it's zero, red. I'm not sure how to create this macro, and other sources I've looked into have been really unhelpful. I'm running on Excel 2010.

message edited by stand.lauren


See More: Conditional Formatting of a Row

Report •


#1
August 15, 2013 at 11:01:55
Do you really need a macro or will Conditional Formatting do?

Let's say your data starts in A2:L2.

Select A2:L2.

Apply these 2 CF rules to the range:

=$L2=1 (Format as Green)
=AND($L2=0,$L2<>"") (Format as Red)

Test it to see that works by putting a 1, 0 or any other value in L2.

Once you have it working, select A2:L2 and click the Format Painter button.

Drag the paint brush over the other cells where you want this happen, e.g. A3:L10.

You should be all set.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
August 15, 2013 at 11:13:03
✔ Best Answer
You really don't need a Macro to do this. Conditional Formatting works quite well.

This is for 2007, but should be the same for 2010

You will need 2 rules, one for green one for red

For Green
1) Select your range of cells A1 thru L1
2) On the Ribbon Select Home Tab
3) On the Ribbon click Conditional Formatting
4) Click on New Rules, it’s near the bottom of the dialog box.
5) Click Use Formula to determine which cells to format.
6) Enter the formula:

=$L1=1 << Note the Dollar Sign - it is required.

6) Click on the Format button
7) Select the Fill Tab
8) Select a Green color
9) Click OK
10) Click OK

For Red
1) Select your range of cells A1 thru L1
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.
5) Enter the formula:

=AND($L1<>"",$L1=0) << Note the Dollar Sign - it is required.

6) Click on the Format button
7) Select the Fill Tab
8) Select a Red color
9) Click OK
10) Click OK

For more than just Row 1, simply select Cells A1 thru L1
and drag down.

See how that works.

MIKE

http://www.skeptic.com/


Report •

#3
August 15, 2013 at 11:54:32
mmcconaghy:

That worked, except it didn't format that first row that I input the rule for; is that normal?

message edited by stand.lauren


Report •

Related Solutions

#4
August 15, 2013 at 12:06:12
And then when I applied it to my spreadsheet, it turned my zeroes green and did nothing for my ones.

Report •

#5
August 15, 2013 at 12:47:04
I followed Mike's suggestion as well as my own and they both worked fine.

Since Mike's instructions are much more detailed than mine, try following them exactly and see what happens.

First, since you tried something already, I suggest you Clear all of your rules first so that you don't confuse things.

Conditional Formatting
Clear Rules
Clear Rules From Entire Sheet (assuming you don't have any other CF rules that you want left intact)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question