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

Report
-
Posts
2669
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2020
-
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
2669
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2020
446
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
1
Thank you

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 2942 users have said thank you to us this month

Posts
2669
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2020
446
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.