Changing font colour

Solved/Closed
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
Hi,
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")
ActiveSheet.Unprotect
'--
'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
Next
aRange.Select
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.
Thanks

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.
7
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 ?
0
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.
0
hi,

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.....
0
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
?
0