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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

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.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,605
Messages
5,765,385
Members
425,281
Latest member
tmoreira001

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