CODEWITHSUNDEEP
excelvba
Dean Johns
Dean Johns

Reputation: 498

How to format cells based on their values in Excel

I have this logic to give cell colors for selected cell range with values.

If value > 75
  cell color = BLUE
Else if value > 50
  cell color = RED
Else
  cell color = BLACK

how can I use this logic in excel to color cells based on their values?

Upvotes: 0

Views: 165

Answers (1)

Tuberose
Tuberose

Reputation: 444

Macro

Write this in the appropriated Sheet's module (+Alt + F11)

Updated

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Target.Value > 75 Then
        Target.Interior.Color = 12611584
    ElseIf Target.Value > 50 Then
        Target.Interior.Color = 255
    Else
        Target.Interior.Color = 0
    End If
    Application.EnableEvents = True
End Sub

Conditional formating

  1. Step1 Step1
  2. Step2 Step2

Explain

For Blue

  • Range: Desired. For example using entire Sheet's range in Excel 2007 is =$1:$1048576
  • Formula: Use the first cell of the range. (For above example that is A1): =A1>75

For Red

  • Range: Desired. For example using entire Sheet's range in Excel 2007 is =$1:$1048576
  • Formula: Use the first cell of the range. (For above example that is A1):=A1>50

For Black

  • Range: Desired. For example using entire Sheet's range in Excel 2007 is =$1:$1048576
  • Formula: Use the first cell of the range. (For above example that is A1):=AND(NOT(A1>50),ISNUMBER(A1))

Upvotes: 2

Related Questions