Changing font colour

Ancon - May 19, 2009 at 09:03 AM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Aug 5, 2010 at 06:23 AM
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.
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 ?
sarbel.vilo Posts 233 Registration date Tuesday November 18, 2008 Status Member Last seen August 1, 2010 31
May 19, 2009 at 09:28 AM
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.....
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Aug 5, 2010 at 06:23 AM
Hari, so once changed would the color remain red for ever