I've been scratching my head on this one, and can't find the answer. Is there a way to fill a cell with shading or color based on a result of a formula?
Any help would be appreciated.
I've been scratching my head on this one, and can't find the answer. Is there a way to fill a cell with shading or color based on a result of a formula?
Any help would be appreciated.
yes, it's called "conditional formatting"
that is, formatting based on a condition that you specify
for example, you want cell A1 to be red *IF* the value in the cell is equal to 10 :
select A1
format
conditional formatting
cell value : is equal to : 10
format
patterns
ferrari red
ok
and it should fill A1 red each time it equals 10
when you get this right, play around with the formats - they can be colours, fonts, borders etc etc
also, then change "cell value is" to "formula is" which conditional formats based on the result of a formula (not the formula in the cell)
ie =row()=12
where the result is always a boolean true or false, so no need for any IFs
say, for example, you wanted A1 to be red if the value in A1 was less than the value in B1
since A1 and B1 could always be different values, you can't hard code this into the "cell value is" part of conditional formatting.... you'll need to use "formula is"
using "formula is", on the most part, requires a boolean formula
an example of a boolean formula would be :
in A1, type =B1
if B1 contains 7, you will get 7 in A1
now, in A1, type =B1=7
you are telling excel that B1 equals 7
if B1 is blank, it should say "FALSE".... it disagrees with your bold statement that B1 = 7
if you put a 7 in B1 the formula will now return a TRUE.... excel is agreeing with your statement
in a nutshell, this is boolean - you specify the arguement and excel bring back a boolean TRUE or FALSE
it is these TRUEs that we input as formulae in conditional formatting, and when they *are indeed* true, the fomatting kicks in
so back to the original example, if we select "formula is" and tell it =A1 < B1, whenever this statement is TRUE, it should apply your formatting, and whenever it's FALSE, it should omit the formatting
another example :
"formula is" =weekday(today())=7 formatted to red will turn that cell red whenever its Saturday
(today being saturday)
...editted to turn off HTML
Don’t have an account yet? Register yourself now and be a part of our community!