VBA to change color of numbers but not text

THXman01

New Member
Joined
Feb 25, 2021
Messages
6
Office Version
  1. 2019
Platform
  1. Windows
Hello and thank you in advance for your help! I'm trying to create an Excel VBA macro that changes the color of NUMBERS based on criteria, but does NOT change the color of TEXT. Specifically, I wanted to change only cells with NUMBERS, whether they are hardcoded or the result of a formula, based on the following criteria:
  • Hardcoded: BLUE
  • Link to another tab within the same workbook: GREEN
  • Link to the same tab within the same workbook: MAROON
  • Link to another workbook file: RED
  • Formula: BLACK
I've gotten so far as to recolor everything, including numbers and text, but cannot figure out how to restrict this macro to only change the color of numbers, and not make any changes to cells with text or a combination of text and numbers. Here's my VBA:

VBA Code:
Sub Finance_Model_Color_Coder()
    Dim cell As Range
    Sheet1.UsedRange.SpecialCells(xlCellTypeConstants).Font.Color = RGB(0, 0, 255) 'blue
    For Each cell In Sheet1.UsedRange.SpecialCells(xlCellTypeFormulas)
     If InStr(1, cell.Formula, "!") > 0 Then cell.Font.Color = RGB(50, 205, 50) 'lime green
     If InStr(1, cell.Formula, "!") = 0 Then cell.Font.Color = RGB(128, 0, 0) 'maroon
     If InStr(1, cell.Formula, "!") = 0 And InStr(1, cell.Formula, "(") > 0 Then cell.Font.Color = RGB(0, 0, 0) 'black
     If InStr(1, cell.Formula, "[") > 0 Then cell.Font.Color = RGB(255, 0, 0) 'red
    Next cell
End Sub

Any help you could provide in updating this code so it only changes the colors of numbers, and not text or combination of text and numbers within a cell, would be really helpful. Thank you!
 

THXman01

New Member
Joined
Feb 25, 2021
Messages
6
Office Version
  1. 2019
Platform
  1. Windows
Fluff.... YEESSSS! Thank you so much for your quick help! That did it!
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,794
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,129
Messages
5,628,869
Members
416,347
Latest member
AT2021

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