filling a cell color within a formula

  • 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
    conditional formatting
    cell value : is equal to : 10
    ferrari red

    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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!