VBA font red coloured entire column range if single value.

[motilulla]

Hello,

I need VBA help; I have data range C2:J51 total 8 columns out of 8 if any entire column has only single value in it make font colours red.

In this given example there are 3 columns which has in the entire column single value in the column C=G, in the column E=5 and in the column H=9 so i want these columns font should be show in red rest columns in 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	5	5
3			G	5	5	5	5	9	5	5
4			G	5	5	5	5	9	5	5
5			G	5	5	5	5	9	5	5
6			G	G	5	5	5	9	5	5
7			G	G	5	5	5	9	5	5
8			G	9	5	5	5	9	5	5
9			G	5	5	G	5	9	5	5
10			G	G	5	G	5	9	5	5
11			G	9	5	G	5	9	5	5
12			G	5	5	9	5	9	5	5
13			G	G	5	9	5	9	5	5
14			G	9	5	9	5	9	5	5
15			G	5	5	5	G	9	5	5
16			G	G	5	5	G	9	5	5
17			G	9	5	5	G	9	5	5
18			G	9	5	5	G	9	5	5
19			G	5	5	G	G	9	5	5
20			G	G	5	G	G	9	5	5
21			G	9	5	G	G	9	5	5
22			G	5	5	9	G	9	5	5
23			G	G	5	9	G	9	5	5
24			G	9	5	9	G	9	5	5
25			G	5	5	5	9	9	5	5
26			G	G	5	5	9	9	5	5
27			G	9	5	5	9	9	5	5
28			G	5	5	G	9	9	5	5
29			G	G	5	G	9	9	5	5
30			G	9	5	G	9	9	5	5
31			G	5	5	9	9	9	5	5
32			G	G	5	9	9	9	5	5
33			G	9	5	9	9	9	5	5
34			G	5	5	5	5	9	G	5
35			G	G	5	5	5	9	G	5
36			G	9	5	5	5	9	G	5
37			G	5	5	G	5	9	G	5
38			G	G	5	G	5	9	G	5
39			G	9	5	G	5	9	G	5
40			G	5	5	9	5	9	G	5
41			G	G	5	9	5	9	G	5
42			G	G	5	9	5	9	G	5
43			G	9	5	9	5	9	G	5
44			G	5	5	5	G	9	G	5
45			G	G	5	5	G	9	G	5
46			G	9	5	5	G	9	G	5
47			G	5	5	G	G	9	G	5
48			G	G	5	G	G	9	G	5
49			G	9	5	G	G	9	G	5
50			G	5	5	9	G	9	G	5
51			G	G	5	9	G	9	G	5
52
53
54

Thank you all.

I am using Excel 2000

Regards,
Moti

 

[bobsan42]

Would you care for a ConditionalFormatting solution:

MrExcel Playbook 02 2021-09.xlsm
	C	D	E	F	G	H	I	J
1	Data1	Data2	Data3	Data4	Data5	Data6	Data7	Data8
2	G	5	5	5	5	9	5	5
3	G	5	5	5	5	9	5	5
4	G	5	5	5	5	9	5	5
5	G	5	5	5	5	9	5	5
6	G	G	5	5	5	9	5	5
7	G	G	5	5	5	9	5	5
8	G	9	5	5	5	9	5	5
9	G	5	5	G	5	9	5	5
10	G	G	5	G	5	9	5	5
11	G	9	5	G	5	9	5	5
12	G	5	5	9	5	9	5	5
13	G	G	5	9	5	9	5	5
14	G	9	5	9	5	9	5	5
15	G	5	5	5	G	9	5	5
16	G	G	5	5	G	9	5	5
17	G	9	5	5	G	9	5	5
18	G	9	5	5	G	9	5	5
19	G	5	5	G	G	9	5	5
20	G	G	5	G	G	9	5	5
21	G	9	5	G	G	9	5	5
22	G	5	5	9	G	9	5	5
23	G	G	5	9	G	9	5	5
24	G	9	5	9	G	9	5	5
25	G	5	5	5	9	9	5	5
26	G	G	5	5	9	9	5	5
27	G	9	5	5	9	9	5	5
28	G	5	5	G	9	9	5	5
29	G	G	5	G	9	9	5	5
30	G	9	5	G	9	9	5	5
31	G	5	5	9	9	9	5	5
32	G	G	5	9	9	9	5	5
33	G	9	5	9	9	9	5	5
34	G	5	5	5	5	9	G	5
35	G	G	5	5	5	9	G	5
36	G	9	5	5	5	9	G	5
37	G	5	5	G	5	9	G	5
38	G	G	5	G	5	9	G	5
39	G	9	5	G	5	9	G	5
40	G	5	5	9	5	9	G	5
41	G	G	5	9	5	9	G	5
42	G	G	5	9	5	9	G	5
43	G	9	5	9	5	9	G	5
44	G	5	5	5	G	9	G	5
45	G	G	5	5	G	9	G	5
46	G	9	5	5	G	9	G	5
47	G	5	5	G	G	9	G	5
48	G	G	5	G	G	9	G	5
49	G	9	5	G	G	9	G	5
50	G	5	5	9	G	9	G	5
51	G	G	5	9	G	9	G	5
motilulla

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:J51	Expression	=COUNTA(C$2:C$51)=COUNTIF(C$2:C$51,C$2)	text	NO

 

[motilulla]

Would you care for a ConditionalFormatting solution:

MrExcel Playbook 02 2021-09.xlsm
	C	D	E	F	G	H	I	J
1	Data1	Data2	Data3	Data4	Data5	Data6	Data7	Data8
2	G	5	5	5	5	9	5	5
3	G	5	5	5	5	9	5	5
4	G	5	5	5	5	9	5	5
5	G	5	5	5	5	9	5	5
6	G	G	5	5	5	9	5	5
7	G	G	5	5	5	9	5	5
8	G	9	5	5	5	9	5	5
9	G	5	5	G	5	9	5	5
10	G	G	5	G	5	9	5	5
11	G	9	5	G	5	9	5	5
12	G	5	5	9	5	9	5	5
13	G	G	5	9	5	9	5	5
14	G	9	5	9	5	9	5	5
15	G	5	5	5	G	9	5	5
16	G	G	5	5	G	9	5	5
17	G	9	5	5	G	9	5	5
18	G	9	5	5	G	9	5	5
19	G	5	5	G	G	9	5	5
20	G	G	5	G	G	9	5	5
21	G	9	5	G	G	9	5	5
22	G	5	5	9	G	9	5	5
23	G	G	5	9	G	9	5	5
24	G	9	5	9	G	9	5	5
25	G	5	5	5	9	9	5	5
26	G	G	5	5	9	9	5	5
27	G	9	5	5	9	9	5	5
28	G	5	5	G	9	9	5	5
29	G	G	5	G	9	9	5	5
30	G	9	5	G	9	9	5	5
31	G	5	5	9	9	9	5	5
32	G	G	5	9	9	9	5	5
33	G	9	5	9	9	9	5	5
34	G	5	5	5	5	9	G	5
35	G	G	5	5	5	9	G	5
36	G	9	5	5	5	9	G	5
37	G	5	5	G	5	9	G	5
38	G	G	5	G	5	9	G	5
39	G	9	5	G	5	9	G	5
40	G	5	5	9	5	9	G	5
41	G	G	5	9	5	9	G	5
42	G	G	5	9	5	9	G	5
43	G	9	5	9	5	9	G	5
44	G	5	5	5	G	9	G	5
45	G	G	5	5	G	9	G	5
46	G	9	5	5	G	9	G	5
47	G	5	5	G	G	9	G	5
48	G	G	5	G	G	9	G	5
49	G	9	5	G	G	9	G	5
50	G	5	5	9	G	9	G	5
51	G	G	5	9	G	9	G	5
motilulla

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:J51	Expression	=COUNTA(C$2:C$51)=COUNTIF(C$2:C$51,C$2)	text	NO

bobsan42, yes CF solution: worked perfect, after Appling CF also noticed in my example image there is a mistake column J=5 as it has single value in it has to font red.

I appreciate your help but some how I need VBA please if you can.

Good Luck

Kind Regards,
Moti

 

[bobsan42]

try this code:

Option Explicit

Sub motilulla_RedColumns()
    Dim rng As Range
    Set rng = ActiveSheet.Range("C2:J51")
    Dim cc As Range, i As Long
    
    For i = 1 To rng.Columns.Count
        Set cc = rng.Columns(i)
        With Application.WorksheetFunction
            If .CountA(cc) = .CountIf(cc, cc.Cells(1, 1).Value) Then
                cc.Font.Color = vbRed
            Else
                cc.Font.Color = 0
            End If
        End With
    Next i

    Set rng = Nothing
    Set cc = Nothing
End Sub

 

[motilulla]

try this code:

Option Explicit

Sub motilulla_RedColumns()
    Dim rng As Range
    Set rng = ActiveSheet.Range("C2:J51")
    Dim cc As Range, i As Long
   
    For i = 1 To rng.Columns.Count
        Set cc = rng.Columns(i)
        With Application.WorksheetFunction
            If .CountA(cc) = .CountIf(cc, cc.Cells(1, 1).Value) Then
                cc.Font.Color = vbRed
            Else
                cc.Font.Color = 0
            End If
        End With
    Next i

    Set rng = Nothing
    Set cc = Nothing
End Sub

bobsan42, your macro worked like charm. ?

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

Good Luck, have a great time

Kind Regards,
Moti

 

[bobsan42]

Glad i could help. Cheers and good night.