VBA to change font color in cells based on value

[KP_SoCal]

I'm using some basic code below in an on Workbook Open event to format cells with a value less then 2 and less than 1 with a particular color.

The code works, but it really slows my worksheet down when opening. Is there better way to write this? Thanks!

Dim myRange As Range
Dim cell As Range
Set myRange = Range("V6:V50000")
    For Each cell In myRange
    If cell.Value < 2 Then cell.Font.ColorIndex = 5
    If cell.Value < 1 Then cell.Font.ColorIndex = 3
    Next

 

[sanrv1f]

do you really need VBA?

see if this helps

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

 

[KP_SoCal]

Hi Sankar, I'm familiar with conditional formatting. For specific reasons, I can not accomplish what I need using the conditional formatting for the particular spreadsheet I'm working on, which is why I'm resorting to some standard formatting code.

 

[sanrv1f]

may be this

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Dim myRange As Range
Dim cell As Range
Set myRange = Range("V5:V50000")
myRange.AutoFilter 1, "<2"
myRange.Parent.AutoFilter.Range.Cells.Font.ColorIndex = 5
myRange.Parent.AutoFilterMode = False
myRange.AutoFilter 1, "<1"
myRange.Parent.AutoFilter.Range.Cells.Font.ColorIndex = 3
myRange.Parent.AutoFilterMode = False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

 

[KP_SoCal]

Sankar,
Excellent! It absolutely works and your code executes much faster than my original code. Thanks so much for your help!!!

KP

 

[adammark]

How can I change this code to color based on a test entry?

I want to format days of the week to different colors.