Andrew has attended:
Excel Advanced course
Excel VBA Intro Intermediate course
Excel Advanced - For Power Users course
Excel Dashboards for Business Intelligence course
Conditional Formatting
Is it possible to format a cell based on the value of a different cell?
E.g. If Cell B1 = 4 then A1 should be yellow, if it is 3 then A1 should be red and so on?
I suspect that this maybe a vba question?
Thanks,
Andy
RE: Conditional Formatting
Hi Andrew,
It is indeed possible to format one cell based on another's value.
Say you want to use cells A1 and A2. Select cell A2. In conditional formatting, select "Use a formula to determine which cells to format".
In the formula field, you type your formula. (E.g. if cell A1 has a value and you want to shade cell A2)
=$A1=1
That is the formula. Now click the Format button and assign a colour.
You have to create more colour rules for different numbers in cell A1. (e.g. =$A1=2).
You'll notice there's no $ before the row number in the formula. This enables you to copy the formatting down a column.
When you enter the number into A1, A2's colour will change.
I hope this helps.
Kind regards
Marius Barnard
Excel Trainer
RE: Conditional Formatting
Thanks Marius, what if the cell I'm trying to colour (which is where is) has text inside it?
In my workbook the scenario is like this:
J2 has text and K2 has a number - the only place I can write the formula is in J2 is it not?
Also, I have numbers ranging from 1 to 7 so this will require an IF formula.
RE: Conditional Formatting
Hi Andy,
Just to clarify, you enter the formula inside the Conditional Formatting box, not in a cell, so it doesn't matter if the cell contains data. Also, you don't need an IF function, you create separate rules for the different colours.
If you like, you could email an extract from your spreadsheet to forum@stl-training.co.uk, then we could format it so that you can see the rules in action.
Kind regards
Marius
