VBA Function code to highlight Odd and Even numbers [Solved]

Report
-
Posts
2693
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 18, 2021
-
Hello,
I am trying to write a VBA Function code to highlight Odd and Even numbers. But not having much luck. Can anyone help me, please?

I would like to use the following condition to highlight a cell:

=AND(ISODD(A1), A1<24) for Low-Odds
=AND(ISODD(A1), A1>24) for Low-Evens
=AND(ISEVEN(A1), A1<23) for High-Odds
=AND(ISEVEN(A1), A1>23) for High-Evens

2 replies

Posts
2693
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 18, 2021
455
Hi Kalpesh,

Well then it makes perfect sense to use VBA.

You didn't provide much details, so this is what the code below does:

- Check each cell in column A starting in A1 untill the last cell used.
- The cells will be colored according to your 4 conditions. The colors will be respectively Red, Yellow, Green and Blue.
- The result of the counted colors will be put in the range C1:C4, of which the cells are colored as well for easy reference.

Here is the code:
Sub RunMe()
Dim cRed, cYellow, cGreen, cBlue As Integer

For Each cell In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    If cell.Value < 24 And cell.Value Mod 2 = 1 Then
        cell.Interior.Color = vbRed
        cRed = cRed + 1
    End If
    If cell.Value > 24 And cell.Value Mod 2 = 1 Then
        cell.Interior.Color = vbYellow
        cYellow = cYellow + 1
    End If
    If cell.Value < 23 And cell.Value Mod 2 = 0 Then
        cell.Interior.Color = vbGreen
        cGreen = cGreen + 1
    End If
    If cell.Value > 23 And cell.Value Mod 2 = 0 Then
        cell.Interior.Color = vbBlue
        cBlue = cBlue + 1
    End If
Next cell

Range("C1").Value = cRed
Range("C1").Interior.Color = vbRed
Range("C2").Value = cYellow
Range("C2").Interior.Color = vbYellow
Range("C3").Value = cGreen
Range("C3").Interior.Color = vbGreen
Range("C4").Value = cBlue
Range("C4").Interior.Color = vbBlue

End Sub


Let us know if alterations are desired.

Best regards,
Trowa
Posts
2693
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 18, 2021
455
Hi Kalpesh,

Why would you want a VBA code, when this is clearly a job for Conditional Formatting? Have you tried that?

Best regards,
Trowa
Yes, I have, But I also wanted to count the number of cells represented by each color. Unfortunately, the VBA code I tried to count these cells doesn't work with conditional formating.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!