Conditional formatting based of value in another cell

Golaidron

Board Regular
Joined
Jan 23, 2013
Messages
83
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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
You would need to create a rule for each cell.
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
cool , never thought of that , even better, and doesnt matter whats in the cell either, so brilliant
 
Last edited:
Upvote 0
@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.
 
Upvote 0
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
 
Upvote 0
Solution

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

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
Back
Top