4 Conditions For Conditional Formatting

  • Hi,


    Sorry about the tongue twister of a title.


    I am trying to conditionally format a cell with the following parameters:


    If empty no formatting
    If cell value = 0 then turn orange
    If cell value < 0 then turn red
    If cell value > 0 then turn green


    I know this is technically 4 conditions (of which excel 2003 accepts only 3) but I feel that there must be a formula of some sort that could achieve this.


    Thanks in advance.


    -Upside

    I am new to VBA - comments on how to improve my code are always welcome.

  • Re: 4 Conditions For Conditional Formatting


    The problem is an empty cell is returned as a 0 therefore the condition to turn the cell orange when 0 is true means that it is always orange when I don't want any formatting applied.

    I am new to VBA - comments on how to improve my code are always welcome.

  • Re: 4 Conditions For Conditional Formatting


    That's close, junho.


    • Default color: none
    • Conditional formatting:
      [INDENT]
    • Formula1: [COLOR="Blue"]=AND(ISNUMBER(A1), A1<0)[/COLOR] (red)
    • Formula2: [COLOR="Blue"]=AND(ISNUMBER(A1), A1=0)[/COLOR] (orange)
    • Formula3: [COLOR="Blue"]=AND(ISNUMBER(A1), A1>0)[/COLOR] (green)[/INDENT]

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

  • Re: 4 Conditions For Conditional Formatting


    Its also possible to use custom number formats to some extent, eg:


    Format>Cells>Custom> [Blue][=0]#,##0;[Red][<0]-#,##0;[Green] #,##0;[Magenta]@


    This would make zero values BLUE, negative numbers RED, positive numbers GREEN, text MAGENTA



    Conditional Formating could also be used that would overide the cell formating, eg:


    Condition1: CellValueIs 0 (Orange)
    Condition2: FormulaIs: =AND(ISNUMBER(A1), A1>=100) (Aqua)
    Condition3: FormulaIs: =AND(ISNUMBER(A1), A1>=10) (Lavender)


    Perhaps read here for more http://www.ozgrid.com/Excel/font-formats.htm

  • Re: 4 Conditions For Conditional Formatting


    Another solution (I think more elegant in respect that it would negate the need for two extra helper columns in my sheet) would be comparing the Vlookup values of two cells (BI5 & BM5) which is then used to conditionally format the cells:


    =IF(VLOOKUP(BI5,VLookupTable!$G$2:$H$7,2)>=(VLOOKUP(BM5,VLookupTable!$G$2:$H$7,2)))


    the above would turn green, OR


    =IF(VLOOKUP(BI5,VLookupTable!$G$2:$H$7,2)<(VLOOKUP(BM5,VLookupTable!$G$2:$H$7,2)))


    would turn red.


    It returns an error however - can someone spot the mistake?

    I am new to VBA - comments on how to improve my code are always welcome.

  • Re: 4 Conditions For Conditional Formatting


    Your syntax, [COLOR="Blue"]=IF(Condition)[/COLOR], returns 0 if Condition is true and FALSE if Condition is false.


    You could use [COLOR="Blue"]=SIGN(A-B)[/COLOR], which would return -1, 0, or 1.

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

  • Re: 4 Conditions For Conditional Formatting


    Thanks

    I am new to VBA - comments on how to improve my code are always welcome.

Participate now!

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