# Conditional formatting based of value in another cell

#### Golaidron

##### Board Regular
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
You would need to create a rule for each cell.

Ok, thanks.

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

#### etaf

##### Well-known Member

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

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

Last edited:

#### Golaidron

##### Board Regular
@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
+Fluff 1.xlsm
FGHIJK
112
125
13
141
15
Main
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G11:K14Expression=COLUMNS(\$G11:G11)<=\$F11textNO

#### Golaidron

##### Board Regular
Thanks you sooooo much.
You are the guru of Excel.

Replies
5
Views
47
Replies
3
Views
37
Replies
8
Views
39
Replies
12
Views
136
Replies
2
Views
285

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.

### Which adblocker are you using?

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

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