"If" and Syntax to change font and fill (interior) color based on cell value

[jdfjab]

How do I structure my If statement to change the font and fill color if the cell value is between -500 and 500? I have the following code which works with debug.print where I would like to change the font and fill colors.

ThisWorkbook.Worksheets("lastmaintenance").Range("M1").Select
    
    nOffSetRow = 1
    
    Do Until ThisWorkbook.Worksheets("lastmaintenance"). _
    Range("M1").Offset(nOffSetRow).Value = ""
    
       MileageSvcDo = CInt(ThisWorkbook.Worksheets("lastmaintenance"). _
       Range("M1").Offset(nOffSetRow).Value)
       
       'what is the syntax to
       'change the font(to color -16383844)
       'and fill color (to color 13551615)
       'if the value of MileageSvcDo
       'is > -500 And < 500
       
       End If
           
    nOffSetRow = nOffSetRow + 1
    
    Loop

[JBeaucaire]

Why are you using VBA? Conditional formatting on that range will allow you to set that up quickly and it will run in realtime automatically.

[jdfjab]

It's part of a longer sub that clears and rebuilds the sheet "lastmaintenance". The conditional formating rules "increase" in number with new ranges every time the sub is run.

[JBeaucaire]

Turn on the macro recorder and let it record you applying CF to that column based on the current number of rows of data. Post the resulting code here and we can help you tweak that into a more dynamic version that will apply to the existing range of data each time.

[jdfjab]

Here is the Macro recorded code, except it must exclude value 0 or "" also

'
' FormatColor Macro
'

'
    Columns("M:M").Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="=-500", Formula2:="=500"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

[JBeaucaire]

Record a macro of you applying ALL of the conditional formatting rules needed to get the results you need. Once you have all that figured out and working, show us THAT code.

[jdfjab]

Thanks.
Here is what I ended up with:

'/===========================================================
'   Begin mod - 2/16/12 Highlight cells in sheet LastMaintenance
'   Service do schedules
'/===========================================================
    ThisWorkbook.Worksheets("lastmaintenance").Range("M1").Select
    
    nOffSetRow = 1 'initialize row offset
    
    Do Until ThisWorkbook.Worksheets("lastmaintenance"). _
    Range("M1").Offset(nOffSetRow).Text = ""
    
        MileageSvcDo = ThisWorkbook.Worksheets("lastmaintenance"). _
        Range("M1").Offset(nOffSetRow).Text
           
           If IsNumeric(MileageSvcDo) = True Then 'verify as number
               If MileageSvcDo > -500 And MileageSvcDo < 500 _
               And MileageSvcDo <> 0 Then
                   'Format cell color for service do requirement
                   ThisWorkbook.Worksheets("lastmaintenance"). _
                   Range("M1").Offset(nOffSetRow).Interior.Color = 13551615
                   ThisWorkbook.Worksheets("lastmaintenance"). _
                   Range("M1").Offset(nOffSetRow).Font.Color = -16383844
                End If
            End If
           
    nOffSetRow = nOffSetRow + 1 'increment row offset
    
    Loop
'/===========================================================
'  End mod - 2/16/12 Highlight cells in sheet LastMaintenance
'  Service do schedules
'/===========================================================

[JBeaucaire]

Can you provide a link to the other forum posts where that was worked out? Thanks.