VBA help make font colour red in the entire column

[motilulla]

Hello,

My data are in range C2:J51 that has always fix value in each column 5, 9 and G

I need VBA help; I have data range C2:J51 total 8 columns out of 8 if any entire column has 1 or 2 values (not all 3) make the font colour red.

In this given example there are 5 columns in which there are 1 or 2 vale...
Column C has 2 values G&9 colour this column font in red.
Column E has 1 value, which is 5, colours this column font in red.
Column G has 2 values 5&G colour this column font in red.
Column H has 1 value, which is 9, colours this column font in red.
Column I has 2 values G&9 colour this column font in red.

Rest 3 columns D, F J has all 3 values 5, 9 and G so far leave it font colour black

For more detail the image is attached.

*	A	B	C	D	E	F	G	H	I	J	K	L
1			Data1	Data2	Data3	Data4	Data5	Data6	Data7	Data8
2			G	5	5	5	5	9	G	G
3			G	5	5	5	5	9	9	G
4			G	5	5	5	5	9	9	G
5			G	5	5	5	5	9	9	G
6			G	5	5	5	5	9	G	G
7			G	5	5	5	5	9	9	G
8			G	5	5	5	G	9	9	G
9			G	5	5	5	5	9	9	G
10			9	5	5	5	5	9	9	G
11			9	5	5	5	5	9	9	G
12			G	G	5	5	5	9	9	G
13			G	G	5	5	5	9	G	G
14			9	G	5	5	5	9	G	9
15			G	9	5	5	5	9	G	9
16			G	9	5	5	G	9	G	5
17			9	9	5	5	5	9	G	5
18			9	9	5	5	5	9	9	5
19			9	9	5	5	5	9	9	5
20			9	9	5	5	5	9	9	G
21			G	5	5	G	G	9	9	G
22			G	5	5	G	5	9	G	9
23			G	5	5	G	5	9	9	9
24			G	5	5	G	5	9	9	5
25			G	5	5	G	5	9	9	5
26			9	5	5	G	5	9	9	5
27			G	G	5	G	5	9	9	5
28			G	G	5	G	5	9	9	5
29			9	G	5	G	5	9	9	G
30			9	G	5	G	G	9	9	G
31			G	9	5	G	5	9	G	G
32			9	9	5	G	5	9	G	G
33			G	5	5	9	5	9	G	9
34			G	5	5	9	5	9	G	9
35			9	5	5	9	5	9	G	9
36			9	5	5	9	5	9	G	9
37			G	G	5	9	5	9	G	5
38			9	G	5	9	5	9	G	5
39			G	9	5	9	5	9	G	5
40			9	9	5	9	5	9	G	5
41			G	5	5	5	G	9	9	G
42			G	5	5	5	G	9	9	G
43			G	5	5	5	G	9	9	9
44			9	5	5	5	G	9	9	9
45			9	5	5	5	G	9	G	5
46			G	G	5	5	G	9	9	5
47			G	G	5	5	G	9	9	5
48			G	G	5	5	G	9	9	5
49			9	G	5	5	G	9	9	5
50			G	9	5	5	G	9	9	G
51			9	9	5	5	G	9	9	G
52
53
54

Thank you all.

I am using Excel 2000

Regards,
Moti

 

[Akuini]

Try this:

Sub a1181663b()
Dim i As Long, x
Dim c As Range
For i = 3 To 10
    With Range(Cells(2, i), Cells(51, i))
        For Each x In Array("G", 5, 9)
            Set c = .Find(What:=x, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
            If c Is Nothing Then .Font.Color = vbRed: Exit For
        Next
    End With
Next

End Sub

 

[motilulla]

Try this:

Sub a1181663b()
Dim i As Long, x
Dim c As Range
For i = 3 To 10
    With Range(Cells(2, i), Cells(51, i))
        For Each x In Array("G", 5, 9)
            Set c = .Find(What:=x, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
            If c Is Nothing Then .Font.Color = vbRed: Exit For
        Next
    End With
Next

End Sub

Akuini, macro did the job as request fantastic!

Thank you so much for your help and time you took for building a macro solution.

Good Luck

Kind Regards,
Moti

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.