filling a cell color within a formula

  • 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

Participate now!

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