VBA Code for Color Formatting Error

Question

Hi,

I am trying to make a vba code to change the colors of numbers in a given spreadsheet based on the following:

Inputs = blue

Links to other sheets = green

Formulas and others = black

My code is currently as follows:

Sub ColorCodes()

For Each Cell In ActiveSheet.UsedRange

   CF = Cell.Formula

   Cell.Font.Color = RGB(0, 0, 0) 'Black

   If Not Cell.HasFormula Then

     Cell.Font.Color = RGB(0, 0, 255) 'Blue

   ElseIf InStr(1, CF, "!") Then

     Cell.Font.Color = RGB(0, 128, 0) 'Green

   Else:

   End If

NextCell:

 Next

End Sub

My issue is that this is changing the colors of all cells in the worksheet and I only want the numbers to change. Can anyone help? I'm sure this is a simple fix but my VBA knowledge is quite limited.

Thank you!

Answer 1

Just put one IF block

If IsNumeric(Cell.Value) Then

Hence, your macro would be

Sub ColorCodes()
    For Each Cell In ActiveSheet.UsedRange
            If IsNumeric(Cell.Value) Then
                CF = Cell.Formula
                Cell.Font.Color = RGB(255, 0, 0) 'Black
                If Not Cell.HasFormula Then
                Cell.Font.Color = RGB(255, 0, 255) 'Blue
                ElseIf InStr(1, CF, "!") Then
                Cell.Font.Color = RGB(0, 128, 0) 'Green
                Else:
                End If
            End If
NextCell:
        Next
End Sub

Answer 2

In my testing, it is working perfectly all right...May be you can upload your sheet and let me know where the color change is not working, I will be able to work out the problem.

To upload to onedrive - 

1. Optional but recommended - Zip your file.
2. Login to https://onedrive.live.com/ utilizing the same Login ID and Password which you have used on this forum.
3. Open the public folder.
4. Click Upload in the top OR drag and drop the file here.
5. After uploading, right click the file and choose share.
6. Optional but recommended - Uncheck the Allow Editing
7. Click Get a Link.
8. Copy the link and paste the link here.

Answer 3

So I'm trying this but it's not working:

Sub ColorCodesv2()

For Each cell In ActiveSheet.UsedRange

   If IsNumeric(cell.Value) Then

    CF = cell.Formula

        cell.Font.Color = RGB(0, 0, 0) 'Black

        If Not cell.HasFormula Then

            cell.Font.Color = RGB(0, 0, 255) 'Blue

        ElseIf InStr(1, CF, "!") Then

            cell.Font.Color = RGB(0, 128, 0) 'Green

        Else:

        End If

    End If

If WorksheetFunction.IsText(cell) Then

    CF = cell.Formula

    cell.Font.Color = RGB(0, 0, 0) 'Black

    End If

NextCell:

 Next

End Sub

Any advice what I'm doing wrong?

Thanks again for your help I'm sure the questions I'm asking are very basic.

Answer 4

For TEXT in VBA, you can use

WorksheetFunction.IsText

So you can say WorksheetFunction.IsText(Cell)

(2012A will not pass your code as you have IsNumeric)

Answer 5

Thank you so much this works great!

Possible to get a formula that catches any text? Like sometimes I'll have a date like 2012A that I want to be black. It's a numeric value (2012) but has A at the end of it because of custom formatting, right now my formula is still making this blue or green sometimes,