change cell colour using IF

[joey24]

how do i change the cell colour.
Eg, if the value is more than 3, the cell will show exceeded and red colour,
if the value is less than or equal to 3, the cell will show ok and green colour.

 

[Andrew Poulsom]

You can use Conditional Formatting:

http://www.contextures.com/xlCondFormat01.html

 

[Richard Schollar]

Hi

You can use Conditional Formatting:

eg assume your cell has the formula:

=IF(A1>3,"Exceeded",if(A1<=3,"OK","")

Then apply conditional formatting to this cell (Format>Conditional Formatting in xl2003 and below, on Home ribbon in 2007+). Set to Value Is in left hand drop box and type in the value Exceeded in right hand text box. Click Format button and choose Pattern tab and set to red. Click Add condition and ensure Value Is selected and type in OK in text box. Hit Format again, and on Pattern tab select Green. CLick OK.

 

[perco754]

Hi,

Assuming that your workbook is the active one then this code will do the trick. Obs, this will overwrite the cells as I understood your description, if not, just color the cells next to the ones containing the values.
This code assumes that the values to look-up are situated in the A-column.
Change this to whatever column you prefer!

Sub SetCellColorFromValue()

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim i As Integer, lastRow As Integer
    Dim r As Range
    
    Set wb = Application.ActiveWorkbook
    Set ws = wb.ActiveSheet
    
    Set r = ws.Cells(1, 1)
    lastRow = r.End(xlDown).Row
    
    For i = 1 To lastRow
        Set r = ws.Cells(i, 1)
        If r.Value > 3 Then
            r.Interior.ColorIndex = 3
            r.Value2 = "Exceeded"
        Else
            r.Interior.ColorIndex = 35
            r.Value2 = "OK"
        End If
    Next i
End Sub

 

[joey24]

assume this is the formula, =IF(A1>3,"Exceeded",if(A1<=3,"OK","")
if the value is less than zero, how do i leave the cell black instead of showing "OK"?

 

[Richard Schollar]

Just change the formula to:

=IF(A1>3,"Exceeded",if(A1>=0,"OK","")