Color cell based on a value

[darrellx]

I am making a spreadsheet comparing different quotes for building materials.

Is it possible to write a formula to color a cell a specific color when the value in a field is less than a value in another field. For example:

If value in cell b3 is less than cell a3, then color cell b3 yellow.

Thanks in advance.

darrellx

 

[AMAS]

This can be done in vba but I'm not sure if it can be done using conditional formatting.

AMAS

 

[Marcelo Branco]

Hi,

You can use Conditional Formatting

Select B3
Conditional Formatting > New Rule > Format only cells that contain
Fill the boxes with the values below
Cell Value | less than | =A3
Format button
pick Fill ---> yellow
Ok, Ok

HTH

M.

 

[AMAS]

I guess it can be done using conditional formatting. I stand corrected.

AMAS

 

[darrellx]

Thanks for your help.

Darrellx

 

[zuslan]

Please note that conditional formatting has a restriction on number of colors (at least in Excel 2003) to 3. If you need more colors, you can write a simple macro:

Sub add_colors_to_cells()

ActiveCell.Select
Do Until IsEmpty(ActiveCell) 'or any other criteria that indicates when the macro
                             'should stop coloring
    If ActiveCell < Range("A1") Then
        ActiveCell.Interior.ColorIndex = 6
    ElseIf ActiveCell >= Range("A1") And ActiveCell < Range("A2") Then
        ActiveCell.Interior.ColorIndex = 5
    'and so on
    End If
    ActiveCell.Offset(1, 0).Select
Loop

End Sub