Changing font colour

Registration date
Thursday January 28, 2010
Last seen
August 2, 2020
I've looked at some posts on your site and followed the advice, however I can't get this to work.
I'd like to change the font colour of a range of cells depending on whether it contains a formula or text.
Initially all cells in the range contain a formula, but if a formula is overwritten I'd like the colour to change to remind the user that the cell no longer contains a formula.

Sub Lock_AllSnCells()
Dim aCell As Range
Dim bCell As Range
Dim cCell As Range
Set aRange = Range("AA7:AD36")
Set bRange = Range("BC7:BF36")
Set cRange = Range("BN7:BU36")
'For Each aCell In aRange
' If Left(aCell.Formula, 1) <> "=" Then
' aCell.Font.ColorIndex = xlNone
' aCell.Font.Color = vbRed
' End If
'Next aCell
For Each cell In aRange
cell.Font.Color = vbBlue
Next cell
For i = 7 To 36
checkcell = Range("AA" & i).Formula Like "*[A-N]*"
If checkcell = True Then
Range("AA" & i).Font.Color = vbRed
End If
With Selection
Selection.Locked = True
Selection.FormulaHidden = True
End With
Set aCell = Nothing
Set aRange = Nothing

Ranges b and c are repeats of the above.

I've tried a couple of ways with the same result - the sub runs and changes the first cell then stops. No error message, just stops.

Any suggestions would be much appreciated.

3 replies

Sorry, I didn't explain it well enough. Other people are using this spreadsheet and so I wanted to change the colour automatically using a macro. I used conditional formatting but it made the whole workbook too big (file size) and cumbersome. Anyway, I now have the problem sorted - when I deleted the conditional formatting (CF) I missed a couple of cells and that was all it was that was stopping the macro running. I read somewhere that CF caused some problems with macros.
Thanks anyway.
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Hi, I know the use of conditional formatting.It works well if we know the values but I want to change the color of the text in a cell depending on some conditions based on the values in the next column.How can I change the color ?
Registration date
Tuesday November 18, 2008
Last seen
August 1, 2010
Mate i don't get it, i know that if you want to change font colors, go in format cells.

I want to change the font color automatically to red when i am overwriting a value in the cell, where the other remains in black itself.
For ex:
I have 5 cells with diff values in a column. I want to over write the third cell from '3232' to '6000'. can i change this 6000 to red color ?

A1. 560
A2. 659
A3. 3232
A4. 6565
A5. 5000

please advice.....
Registration date
Thursday January 28, 2010
Last seen
August 2, 2020
Hari, so once changed would the color remain red for ever