CODEWITHSUNDEEP
excelvbams-office
Ciara Mo
Ciara Mo

Reputation: 41

Excel VBA Changing Cell Color

I am trying to get all the cells that in column U which NOT equal to 2.04 or 3.59 to change cell color.

Here is the code:

Private Sub Cell_Color_Change()

For Each cell In Range("U2:U19004")
    If cell.Value <> 2.04 Or 3.59 Then cell.Interior.ColorIndex = 3
    Next cell

End Sub

The code turn the whole column red for some reasons. I have tried using the conditional formatting, same thing happened. Please help. Thank you!

Upvotes: 1

Views: 6842

Answers (3)

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96791

Correct your AND and OR:

Private Sub Cell_Color_Change()
    For Each cell In Range("U2:U19004")
        If cell.Value <> 2.04 And cell.Value <> 3.59 Then cell.Interior.ColorIndex = 3
    Next cell
End Sub

enter image description here

EDIT#1:

To look for values rounded to two decimal digits, try this alternative:

Private Sub Cell_Color_Change()

    Dim cv As Variant

    For Each cell In Range("U2:U19004")
        cv = Application.WorksheetFunction.Round(cell.Value, 2)
        If cv <> 2.04 And cv <> 3.59 Then cell.Interior.ColorIndex = 3
    Next cell
End Sub

Upvotes: 2

user10798192
user10798192

Reputation:

Set Conditional Formatting with VBA.

Option Explicit

Private Sub set_Cell_Color_Change()

    With Range("U2:U19004")
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=and(round($u2, 2)<>2.04, round($u2, 2)<>3.59)"
        .FormatConditions(.FormatConditions.Count).Font.Color = vbRed
    End With

End Sub

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152660

  1. Conditional Formatting will do this:
    a. use the formula: AND(U2<>2.04,U2<>3.59)
    b. Choose your fill color
    c. Apply it to U2:U19004

  2. But if you want to code it the if should be:

    If cell.Value <> 2.04 AND cell.Value <> 3.59 Then cell.Interior.ColorIndex = 3

Upvotes: 2

Related Questions