VBA to change font color in cells based on value

KP_SoCal

Board Regular
Joined
Nov 17, 2009
Messages
116
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!

Code:
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
 
Last edited:

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

KP_SoCal

Board Regular
Joined
Nov 17, 2009
Messages
116
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

MrExcel MVP
Joined
Jan 1, 2009
Messages
3,474
Office Version
  1. 2016
Platform
  1. Windows
may be this

Code:
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

Board Regular
Joined
Nov 17, 2009
Messages
116
Sankar,
Excellent! It absolutely works and your code executes much faster than my original code. Thanks so much for your help!!! :)

KP
 

adammark

New Member
Joined
Mar 11, 2009
Messages
4
How can I change this code to color based on a test entry?

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

Forum statistics

Threads
1,181,647
Messages
5,931,208
Members
436,784
Latest member
amuljono

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top