Solved Conditional formatting in a table

Microsoft Excel 2010 - complete product...
May 31, 2016 at 09:29:40
Specs: Windows 10, Cpu
I have a table that has 4 products listed in column G and corresponding % in the following 4 columns. I'm trying to format the % cells to show different colors based on values in a set of other cells. I've tried using an "if" statement but don't know how to write the color formatting in the cell.

Basically I want if cell G6 is = to product x and the value of G7 is > than F7 than highlight cell in green and if < than F7 highlight in red.

I'm trying to figure out and nothing works for me please help if you can


See More: Conditional formatting in a table

Report •


✔ Best Answer
June 2, 2016 at 07:47:47
A minor correction:

In the CF instructions, # 4

For RED it should read:

4) Select Less Than from the menu.

and the formula should be: =MIN($H$2:$H$5)


For Green it should read:

4) Select Greater Than from the menu.

and the formula should be: =MAX($H$2:$H$5)


Sorry, was in a rush and did not double check.


MIKE

http://www.skeptic.com/

message edited by mmcconaghy



#1
May 31, 2016 at 09:46:52
Conditional Formatting only requires that a formula return True.

So a formula like:

=IF(AND(G6=X,G7>F7),TRUE,FALSE)

Should work, provided you add the X value.

This could also be shortened to:

=AND(G6=X,G7>F7)

So now that we have the formula, what do we do with it?

Conditional Formatting 2007 For Green Color

1) Select your cell or range of cells, As Example A1:A100
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(G6=X,G7>F7)

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

Each color will require a different formula, that returns True.

Conditional Formatting 2007 For Red Color

1) Select your cell or range of cells, As Example A1:A100
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(G6=X,G7<F7)

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

And you should probably decide what is going to happen IF/WHEN
G7 = F7


MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#2
May 31, 2016 at 13:53:14
Thank you so much works great.

If I want to copy this over a range of cells where do I put the dollar signs. The greater than or equal measure cells are fixed but the measured cells are throughout the table?


Report •

#3
May 31, 2016 at 14:43:14
Basically I want if cell G6 is = to product x and the value of G7 is > than F7 than highlight cell in green and if < than F7 highlight in red.

The first instruction for CF is:

1) Select your cell or range of cells, As Example A1:A100

What cells do you want to highlight?

Post a small sample of your sheet, please use Column Letters & Row Numbers. Read this How-To which explains the use of the < PRE > tags to align data.

http://www.computing.net/howtos/sho...

I believe your data looks something like:

       G         H      I      J      K
1) My Product  1st %  2nd %  3rd %  4th %
2)  Alpha				
3)  Beta 				
4)  Gamma				
5)  Delta				

Correct?

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
May 31, 2016 at 19:47:12
Yes my data would look similar to your example.
I have % in each column that would then need to be measured against a national number

G
Product. Ytd. Roll 12. Roll 4.
Nation. 3
Nation. 2
Nation. 3
Nation. 2
Product 1. 1
Product 2. 5
Product 3. 4
Product 4. 3

Numbers would be in each column and I need to highlight products 1-4 that are greater or less than the nation.


Report •

#5
June 2, 2016 at 07:04:21
OK, I'm still not sure what your doing, or what your data looks like, but try this.

With your data like:

      G          H        I         J
1) Product      Ytd   Roll 12    Roll 4
2) Nation        3               
3) Nation        2               
4) Nation        3               
5) Nation        2               
6) Product 1     1               
7) Product 2     5               
8) Product 3     4               
9) Product 4     3               

Your CF will be:

1) Select your cell or range of cells, H6:H9
2) On the ribbon click Conditional Formatting
3) Click on Highlight Cell Rules, it’s at the top of the dialog box.
4) Select Greater Than from the menu.
5) In the input box under Format Cell that are Greater Than delete anything that is in there and enter the formula:

=MIN($H$2:$H$5)

6) Click on the color box and select Custom Format it should be at the bottom of the popup menu.
7) Select the Fill Tab
8) Select a Red color
9) Click OK
10) Click OK

For Green, repeat the same process for CF, but change the formula to:

=MAX($H$2:$H$5)

And select a Green color.

You should get:

1 is Red
5 & 4 are Green
3 has no color because it is neither greater than nor less than the MAX or MIN values.
As I mentioned in my first reply:
And you should probably decide what is going to happen IF/WHEN G7 = F7

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#6
June 2, 2016 at 07:20:07
Whitaker,

Mike suggested that you use the pre tags to line up your data so that we can see how your data is formatted. It appears that you did not follow that suggestion.

Please click on the line at the bottom of this post and read the instructions on how to post example data in this forum. This should eliminate the confusion related to your data layout

Click here:

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


Report •

#7
June 2, 2016 at 07:47:47
✔ Best Answer
A minor correction:

In the CF instructions, # 4

For RED it should read:

4) Select Less Than from the menu.

and the formula should be: =MIN($H$2:$H$5)


For Green it should read:

4) Select Greater Than from the menu.

and the formula should be: =MAX($H$2:$H$5)


Sorry, was in a rush and did not double check.


MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#8
June 2, 2016 at 08:28:21
Thank you for your support, even with my inability to follow your Formatting instruction your reply was a great help and I've used your input to solve my problem thank you again for your help

Report •


Ask Question