Conditional formatting based of value in another cell

Golaidron

Board Regular
Joined
Jan 23, 2013
Messages
62
Office Version
  1. 365
Platform
  1. Windows
Hi all.

Do you guys have a simple excel formula that i can use in Conditional formatting.

In Cell A1 i have value from 1-5.
If it's 1 I want B1 to get colored.
If it's 2 I want B1 and C1 to get colored
If it's 3 I want B1, C1 and D1 to get colored
and so on.

Or do i need to create 5 different formula's for this?

Hope to hear from you soon.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,547
Office Version
  1. 365
Platform
  1. Windows
You would need to create a rule for each cell.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,547
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,663
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

Whats in the cells B1, C1 and D1
are they just empty cells , if so you may be able to do something like a gantt chart
If you want ALL the same colour then

So in Cells B1 , C1 & D1
you could say =IF(COLUMN(B1)<=$A1+1,1,"")
And have the font and the fill formatted the same colour

Book4
ABCDEF
13111  
Sheet3
Cell Formulas
RangeFormula
B1:F1B1=IF(COLUMN(B1)<=$A1+1,1,"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B1:F1Expression=B1=1textNO
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,547
Office Version
  1. 365
Platform
  1. Windows
Didn't think of doing it like that, but it can also be done without formulae in the cells like
+Fluff 1.xlsm
ABCDEF
14
Main
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B1:F1Expression=COLUMN()-1<=$A1textNO
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,663
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

cool , never thought of that , even better, and doesnt matter whats in the cell either, so brilliant
 
Last edited:

Golaidron

Board Regular
Joined
Jan 23, 2013
Messages
62
Office Version
  1. 365
Platform
  1. Windows
@Fluff.
Your Conditional Formatting is working when it's in Column A.
But if i move this setup to any place else in the workbook it doesn't work.

In Cell F11 i have value from 1-5.
If it's 1 I want G11 to get colored.
If it's 2 I want G11 and H11 to get colored
If it's 3 I want G11, H11 and I11 to get colored
and so on.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,547
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff 1.xlsm
FGHIJK
112
125
13
141
15
Main
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G11:K14Expression=COLUMNS($G11:G11)<=$F11textNO
 
Solution

Golaidron

Board Regular
Joined
Jan 23, 2013
Messages
62
Office Version
  1. 365
Platform
  1. Windows
Thanks you sooooo much.
You are the guru of Excel.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,528
Messages
5,636,847
Members
416,945
Latest member
Himu

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