Color and remove color from cells

hip2b2

Board Regular
Joined
May 5, 2003
Messages
117
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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,960
Office Version
  1. 365
Platform
  1. Windows
Try this:

VBA Code:
Sub a1157763a()

Range("A:S").SpecialCells(xlCellTypeConstants).Font.ColorIndex = 1
Range("A:S").SpecialCells(xlCellTypeFormulas).Font.ColorIndex = 3
End Sub
 
Solution

hip2b2

Board Regular
Joined
May 5, 2003
Messages
117
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
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,960
Office Version
  1. 365
Platform
  1. Windows
You're welcome, glad to help and thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,127,191
Messages
5,623,289
Members
415,963
Latest member
PatrickDurning

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