Change cell color based on the number in the cell

rplohocky

Board Regular
Joined
Sep 25, 2005
Messages
245
Hello,
I would like to use either conditional formatting, a macro or another method to make a cell(s) turn a specific color based on what number is in a specific cell. An example would be if cell D3 = 1, 13, 25, 37, 49, 61, 73, 84, 97, 109, 121 or 133 then turn cells C and D BLUE. I need this for 12 colors.
List:
if cell D3 = 1, 13, 25, 37, 49, 61, 73, 84, 97, 109, 121 or 133 then turn cells C and D BLUE. (EACH NUMBER IS UNIQUE TO EACH COLOR)
if cell D3 = 2, 14, 26, 38, 50, 62, 74, 85, 98, 110, 122 or 134 then turn cells C and D ORANGE.
if cell D3 = 3, 15, 27, 39, 51, 63, 75, 86, 99, 111, 123 or 135 then turn cells C and D GREEN.
if cell D3 = 4, 16, 28, 40, 52, 64, 76, 87, 100, 112, 124 or 136 then turn cells C and D BROWN.
if cell D3 = 5, 17, 29, 41, 53, 65, 77, 88, 101, 113, 125 or 137 then turn cells C and D SLATE.
if cell D3 = 6, 18, 30, 42, 54, 66, 78, 89, 102, 114, 126 or 138 then turn cells C and D WHITE.
 

Attachments

  • FIBER CHART.JPG
    FIBER CHART.JPG
    21.9 KB · Views: 2

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,671
Office Version
  1. 365
Platform
  1. MacOS
select your range you want coloured

Lets Assumme Column C3 to D100
then select C3 to D100 - NOTE the row in the selection thats starts would need to also be used in the formula

SO as you are using row 3 as the starting point

Select a new Rule for each of the formula for each of the colours

You cannot use arrays in Conditional Formatting then an OR with the numbers OR a table list for each colour and a COUNTIF() =1

if cell D3 = 1, 13, 25, 37, 49, 61, 73, 84, 97, 109, 121 or 133 then turn cells C and D BLUE. (EACH NUMBER IS UNIQUE TO EACH COLOR)
OR ( $D3 =1, $D3 =13, $D3 =25, $D3 =37,$D3 = 49, $D3 =61, $D3 =73,$D3 = 84, $D3 =97, $D3 =109, $D3 =121 ,$D3 = 133 )
Set fill colour for these
New RULE

if cell D3 = 2, 14, 26, 38, 50, 62, 74, 85, 98, 110, 122 or 134 then turn cells C and D ORANGE.
OR( $D3 = 2, $D3 =14,$D3 = 26, $D3 =38, $D3 =50, $D3 =62, $D3 =74, $D3 =85, $D3 =98, $D3 =110, $D3 =122 ,$D3 =134 )
Set fill colour for these
New RULE

if cell D3 = 3, 15, 27, 39, 51, 63, 75, 86, 99, 111, 123 or 135 then turn cells C and D GREEN.
OR ( $D3 = 3, $D3 =15, $D3 =27, $D3 =39, $D3 =51, $D3 =63, $D3 =75, $D3 =86, $D3 =99,$D3 = 111,$D3 = 123 ,$D3 = 135)
Set fill colour for these
New RULE

if cell D3 = 4, 16, 28, 40, 52, 64, 76, 87, 100, 112, 124 or 136 then turn cells C and D BROWN.
OR ( $D3 =4, $D3 =16, $D3 =28, $D3 =40,$D3 = 52, $D3 =64, $D3 =76,$D3 = 87, $D3 =100,$D3 = 112, $D3 =124 ,$D3 =136)
Set fill colour for these
New RULE

if cell D3 = 5, 17, 29, 41, 53, 65, 77, 88, 101, 113, 125 or 137 = then turn cells C and D SLATE.
OR( $D3 =5, $D3 =17, $D3 =29, $D3 =41, $D3 =53, $D3 =65, $D3 =77,$D3 = 88, $D3 =101,$D3 = 113, $D3 =125 ,$D3 = 137)
Set fill colour for these
New RULE

if cell D3 = 6, 18, 30, 42, 54, 66, 78, 89, 102, 114, 126 or 138 then turn cells C and D WHITE.
OR( $D3 = 6, $D3 =18,$D3 = 30,$D3 = 42,$D3 = 54,$D3 = 66, $D3 =78, $D3 =89, $D3 =102,$D3 = 114,$D3 = 126 , $D3 =138)
Set fill colour for these
New RULE

OR use
COUNTIF( Table with that colours numbers in , $D3 ) =1
I Table for each of the colours and 1 rule
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,671
Office Version
  1. 365
Platform
  1. Windows
Assuming that the numbers in the 80s are wrong & should be 85,86 etc, & not 84,85 etc try
+Fluff 1.xlsm
CD
1
2
31
42
53
1311
1412
1513
1614
1715
2523
2624
2725
2826
2927
3735
3836
3937
4038
4139
4947
5048
5149
5250
5351
6159
6260
6361
6462
6563
7371
7472
7573
7674
7775
8583
8684
8785
8886
8987
9088
Master
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C3:D140Expression=MOD($D3-3,12)=0textNO
C3:D140Expression=MOD($D3-2,12)=0textNO
C3:D140Expression=MOD($D3-1,12)=0textNO
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,671
Office Version
  1. 365
Platform
  1. MacOS
i have as an example just show the table and countif with 3 formulas as an example here
Book1
CDEFGHIJKLMNOPQ
2BLUE.ORANGE.GREEN.BROWN.SLATE.WHITE.
31123456
42131415161718
53252627282930
61373839404142
71495051525354
82616263646566
92737475767778
103848586878889
113979899100101102
123109110111112113114
132121122123124125126
143133134135136137138
153
162
171
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C3:D24Expression=COUNTIF($N$3:$N$14,$D3)=1textNO
C3:D24Expression=COUNTIF($M$3:$M$14,$D3)=1textNO
C3:D24Expression=COUNTIF($L$3:$L$14,$D3)=1textNO
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
Hi,

Select the cells you want CF.

Use this formula:

Excel Formula:
=AND($D3>0,$D3<=144,MOD($D3,12)=1)

Change the 1 at the end of the formula to 2, 3, 4, etc. up to 12 for Each of your 12 different colors.

Book3.xlsx
DEFGHIQRSTUV
3-1123456FALSETRUETRUETRUETRUETRUE
4131415161718TRUETRUETRUETRUETRUETRUE
5252627282930TRUETRUETRUETRUETRUETRUE
6373839404142TRUETRUETRUETRUETRUETRUE
7495051525354TRUETRUETRUETRUETRUETRUE
8616263646566TRUETRUETRUETRUETRUETRUE
9737475767778TRUETRUETRUETRUETRUETRUE
10858687888990TRUETRUETRUETRUETRUETRUE
11979899100101102TRUETRUETRUETRUETRUETRUE
12109110111112113114TRUETRUETRUETRUETRUETRUE
13121122123124125126TRUETRUETRUETRUETRUETRUE
14133134135136137138TRUETRUETRUETRUETRUETRUE
15145146147148149150FALSEFALSEFALSEFALSEFALSEFALSE
Sheet890
Cell Formulas
RangeFormula
Q3:Q15Q3=AND($D3>0,$D3<=144,MOD($D3,12)=1)
R3:R15R3=AND($E3>0,$E3<=144,MOD($E3,12)=2)
S3:S15S3=AND($F3>0,$F3<=144,MOD($F3,12)=3)
T3:T15T3=AND($G3>0,$G3<=144,MOD($G3,12)=4)
U3:U15U3=AND($H3>0,$H3<=144,MOD($H3,12)=5)
V3:V15V3=AND($I3>0,$I3<=144,MOD($I3,12)=6)
 
Last edited:

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
Use this formula:

Excel Formula:
=AND($D3>0,$D3<=144,MOD($D3,12)=1)

Change the 1 at the end of the formula to 2, 3, 4, etc. up to 12 for Each of your 12 different colors.

Oops, the above underlined part should say 0 (zero), Not 12, so

Change the 1 at the end of the formula to 2, 3, 4, etc. up to 11, then use 0 for the 12th color, for Each of your 12 different colors.

Let me know if you need further explanation.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,816
Messages
5,638,496
Members
417,029
Latest member
lingx86

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top