Color and remove color from cells

hip2b2

Board Regular
Joined
May 5, 2003
Messages
135
Office Version
  1. 2019
Platform
  1. Windows
VBA Code:
Sub IdentifyFormulaCells()
'Format font in all formula cells RED
  Dim ws As Worksheet
  Dim rng As Range
  Set ws = ActiveSheet
  For Each rng In ws.Cells.SpecialCells(xlCellTypeFormulas)
    rng.Font.ColorIndex = 3 'NB: 3 = Red
  Next rng
End Sub
VBA Code:
Sub UNIdentifyFormulaCells()
' Remove highlight from font in all formula cells.
  Dim ws As Worksheet
  Dim rng As Range
  Set ws = ActiveSheet
  For Each rng In ws.Cells.SpecialCells(xlCellTypeFormulas)
    rng.Font.ColorIndex = 1 'NB: 1= Black
  Next rng
End Sub



I am using these two bits of code to turn the text in cells with formulas Red or Black. Unfortunatly these macros only work on the cells that contain formulas.

What I really need to do is
  1. Within a fixed range Col A - Col S
  2. Change the color of the cells that are formulas to Red - Sub IdentifyFormulaCells()
  3. Change the color of cells that are not formulas to Black - Sub UNIdentifyFormulaCells()
The difference is subtile, but in my case significant

Thanks in advance

hip
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Try this:

VBA Code:
Sub a1157763a()

Range("A:S").SpecialCells(xlCellTypeConstants).Font.ColorIndex = 1
Range("A:S").SpecialCells(xlCellTypeFormulas).Font.ColorIndex = 3
End Sub
 
Upvote 0
Solution
Thanks, you set me on the right track.

"Range("A:S").SpecialCells(xlCellTypeFormulas).Font.ColorIndex = 3", worked perfectly

But for some reason "Range("A:S").SpecialCells(xlCellTypeConstants).Font.ColorIndex = 1" dod not and I settled on using "
Range("a:s").Font.ColorIndex = vbBlack"

Many thanks for the help

hip
 
Upvote 0
You're welcome, glad to help and thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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
Back
Top