Excel conditional formatting using generic formula

1
votes

I want to conditionally format cells in Excel. Is it possible to create a generic formula that does the following:

    IF the value in column A of the given row = "A" 
    AND the value in the given cell is greater than .1 
    AND the value in the given cell is less than 1
    THEN format the cell with a light grey background color

In the example below, I want the formula to apply to cells B1:K2, but I only want cells E1, I1 and K1 to be highlighted in grey.

enter image description here

3
excelexcel-formula
Where could I find an example of such a formula? The examples I've seen test for a single condition and don't refer to the formatted cells in a generic way. - b00kgrrl
Which cells shall the conditional format rule apply to? Which is the "given cell" in your question? - Axel Richter
I've updated the original question with the requested info. - b00kgrrl

3 Answers

1
votes
  • Select B1:K[n]. B1 is the active cell.
  • Then, click Home > Conditional Formatting > New Rule.
  • In the New Formatting Rule dialog box, click Use a formula to determine which cells to format.

  • Under Format values where this formula is true, type the formula:

    =AND($A1="A",B1>0.1,B1<1)

  • Click Format.

  • In the Fill box, select your light gray color.
  • Click OK until the dialog boxes are closed.

Greetings

Axel

0
votes

Tap F5 and when the GoTo dialog opens, type A:K (◄ just like that) into the Reference: box and click OK. You should have columns A through K selected with A1 as the Active Cell.

Create a new Conditional Formatting Rule using the Use a formula to determine which cells to format option and supply the following for Format values where this formula is true: 

=AND($A1="A", A1>0.1, A1<1, ISODD(COLUMN(A:A)))

Click Format and select a light=grey Fill then click OK to accept the format and then OK again to create the new CF rule. Your results should resemble the following.

    enter image description here

Edit: Revised from literal interpretation to subjective interpretation

0
votes

Select cells E1:F2,I1:I2,K1:K2 (hold down Ctrl while selecting discontinuous range) so the conditional formatting will apply to them.

The cell K1 should have the focus, i.e. be highlighted with white background, while other selected cells are highlighted with dark background. Insert conditional formatting with following formula:

=($A1="A")*(K1>0.1)*(K1<1)