Search code examples
excelformatcell

Automatic Conditional Formatting in Excel

I'm trying to this automation as requested by my superior but wasn't able to execute in Excel.

Here's what they want to happen.

Two cells, A1, B1

Conditions to check.

If A1 <> 0 and B1 isblank B1.Cell.format.color shade = yellow. Else B1 color light blue Else if A1 = 0 then b1.cell color shade = white. End

i dont know how to code it.

Please help.

Solution

  • I'm not sure that I understood all the rules you want set but here is a start.

    1. Select B1. Its normal colour is white. Therefore you don't need to colour it white. Therefore you only deal with blue and yellow.
    2. Click CF in the Ribbon > New rule > Use formula to determine which cells to format
    3. In the field Format values where this formula is true enter this formula: =AND(ISBLANK($B$1), $A$1<>0
    4. Click Format > Fill >select yellow. and OK until the dialog closes.
    5. While still selecting B1 repeat step 2-3 but in step 3 enter this formula, =$A$1<>0. Continue with step 4 but set the colour blue.
    6. From the Ribbon's CF drop-down select "Manage Rules". You will see the rules you entered listed. You can edit them here but for now please look at their sequence the rule entered in step 5 must be at the top of the list. If it isn't move it by pressing the arrow buttons above the word Format.
    7. On the right of the list of formats there are check boxes marked Stop if true. Check the first rule (the one setting the blue colour).

    The effect of all this is that CF will examine A1. If there is any value <> 0 in it B1 will turn blue and the next rule won't be applied. But if A1 has a value the next rule will find that B1 is empty and colour the cell yellow - else it will do nothing, meaning B1 retains its original colour, presumably white.