Fill define colours

[Kishan]

Using Excel 2000

Hi,

I got results in cells C8:C70
Define colours in the cell F7:F16 as shown for different 9 patterns.


In the cell C6 got 3-X, I want to fill all 3-X find in Column C with colour is find in cell F15, and only the below the 3-X fill colours as per pattern (colours shown in cells F7:F16)

Example 3-X is in the cells C12:C13 so fill below in the 2-2 colours from as shown in F14

Example data..

Book1
	A	B	C	D	E	F	G
1
2
3
4
5
6			3-X			Fill These
7			Results			Colours
8			1-X			0
9			0			1-1
10			1-1			1-X
11			2-1			1-2
12			3-X			2-1
13			3-X			2-X
14			2-2			2-2
15			2-X			3-X
16			1-1			3-2
17			1-X
18			2-1
19			3-X
20			2-2
21			2-X
22			1-1
23			1-1
24			2-1
25			3-X
26			2-X
27			1-1
28			2-2
29			2-1
30			3-X
31			3-2
32			0
33			2-X
34			2-1
35			3-X
36			2-2
37			1-X
38			0
39			3-X
40			3-X
41			3-X
42			3-X
43			3-X
44			2-X
45			1-2
46			0
47			0
48			3-X
49			1-1
50			1-1
51			2-X
52			2-1
53			3-X
54			1-X
55			2-2
56			2-1
57			3-X
58			0
59			1-1
60			2-1
61			3-X
62			3-X
63			2-X
64			2-2
65			2-1
66			3-X
67			3-X
68			3-X
69			1-X
70			2-X
71
72
Sheet1

Thank you in advance

Regards,
Kishan

 

[Kishan]

Bump

 

[Kishan]

Hi,

I tried to get cell coloured with conditional formatting but get one colour below the
3-X

Please help with VBA

Book1
	A	B	C	D	E	F	G
1
2
3
4
5
6			3-X			Fill These
7			Results			Colours
8			1-X			0
9			0			1-1
10			1-1			1-X
11			2-1			1-2
12			3-X			2-1
13			3-X			2-X
14			2-2			2-2
15			2-X			3-X
16			1-1			3-2
17			1-X
18			2-1
19			3-X
20			2-2
21			2-X
22			1-1
23			1-1
24			2-1
25			3-X
26			2-X
27			1-1
28			2-2
29			2-1
30			3-X
31			3-2
32			0
33			2-X
34			2-1
35			3-X
36			2-2
37			1-X
38			0
39			3-X
40			3-X
41			3-X
42			3-X
43			3-X
44			2-X
45			1-2
46			0
47			0
48			3-X
49			1-1
50			1-1
51			2-X
52			2-1
53			3-X
54			1-X
55			2-2
56			2-1
57			3-X
58			0
59			1-1
60			2-1
61			3-X
62			3-X
63			2-X
64			2-2
65			2-1
66			3-X
67			3-X
68			3-X
69			1-X
70			2-X
71
72
Sheet2

Condition 1 =IF(C$6=C8,1,0)
Contition 2 =IF(C$6=C7,1,0)

Thank you in advance

Regards,
Kishan

 

[Kishan]

Bump

 

[Kishan]

Hi,

Cannot get using conditional formatting, because my version has only 3 conditions could be applied, and I am looking for 9, please help with VBA solution

Thank you in advance

Regards,
Kishan

 

[thisoldman]

Assume all cells in C8:C70 are text, including the zeros.

Use a formula to determine which cells to format

Four rules. Applies to: =$C$8:$C$70
=C8="0"
=LEFT(C8)="1"
=LEFT(C8)="2"
=LEFT(C8)="3"

 

[Kishan]

Assume all cells in C8:C70 are text, including the zeros.

Use a formula to determine which cells to format

Four rules. Applies to: =$C$8:$C$70
=C8="0"
=LEFT(C8)="1"
=LEFT(C8)="2"
=LEFT(C8)="3"

Hi thisoldman, thank you for the giving a conditional formatting formulas

Using as you describe it is giving a result as shown below.

Book1
	A	B	C	D	E	F	G
1
2
3
4
5
6			3-X			Fill These
7			Results			Colours
8			1-X			0
9			0			1-1
10			1-1			1-X
11			2-1			1-2
12			3-X			2-1
13			3-X			2-X
14			2-2			2-2
15			2-X			3-X
16			1-1			3-2
17			1-X
18			2-1
19			3-X
20			2-2
21			2-X
22			1-1
23			1-1
24			2-1
25			3-X
26			2-X
27			1-1
28			2-2
29			2-1
30			3-X
31			3-2
32			0
33			2-X
34			2-1
35			3-X
36			2-2
37			1-X
38			0
39			3-X
40			3-X
41			3-X
42			3-X
43			3-X
44			2-X
45			1-2
46			0
47			0
48			3-X
49			1-1
50			1-1
51			2-X
52			2-1
53			3-X
54			1-X
55			2-2
56			2-1
57			3-X
58			0
59			1-1
60			2-1
61			3-X
62			3-X
63			2-X
64			2-2
65			2-1
66			3-X
67			3-X
68			3-X
69			1-X
70			2-X
71
72
thisoldman

But I want as shown in the post#1 only 3-X in background in yellow and font in black and next to 3-X patterns as shown pre define in the cells F8:F16

Book1
	A	B	C	D	E	F	G
1
2
3
4
5
6			3-X			Fill These
7			Results			Colours
8			1-X			0
9			0			1-1
10			1-1			1-X
11			2-1			1-2
12			3-X			2-1
13			3-X			2-X
14			2-2			2-2
15			2-X			3-X
16			1-1			3-2
17			1-X
18			2-1
19			3-X
20			2-2
21			2-X
22			1-1
23			1-1
24			2-1
25			3-X
26			2-X
27			1-1
28			2-2
29			2-1
30			3-X
31			3-2
32			0
33			2-X
34			2-1
35			3-X
36			2-2
37			1-X
38			0
39			3-X
40			3-X
41			3-X
42			3-X
43			3-X
44			2-X
45			1-2
46			0
47			0
48			3-X
49			1-1
50			1-1
51			2-X
52			2-1
53			3-X
54			1-X
55			2-2
56			2-1
57			3-X
58			0
59			1-1
60			2-1
61			3-X
62			3-X
63			2-X
64			2-2
65			2-1
66			3-X
67			3-X
68			3-X
69			1-X
70			2-X
71
72
Sheet1

I am not sure but this only could be done by only VBA I think so...

Thank you in advance

Regards,
Kishan

 

[Kishan]

Hi,

Any ideas need help please

Thank you in advance

Regards,
Kishan

 

[mikerickson]

If you run this macro, it will set the conditional formatting conditions that you want.
You only have to run it once.
Thereafter, you can copy the formatting to any new cells.
If you change any values in the Fill List, F8:F16, the CF cond
If you change a color in the Fill List, or if you add any cells to the Fill List, you should run the macro again.

Sub test()
    Dim FillList As Range, rngCF As Range
    Dim oneCell As Range, cfCell As Range
    Dim strAddress As String, strFormula As String
    
    With Sheet1
        Set FillList = .Range("F8:F16"): Rem adjust
        Set rngCF = .Range("C:C")
    End With
    rngCF.FormatConditions.Delete
    Set rngCF = rngCF.SpecialCells(xlCellTypeConstants)

    For Each cfCell In rngCF
        With cfCell
            strAddress = .Address(False, False, xlA1)
            For Each oneCell In FillList
               
                strFormula = strAddress & "&"""" = " & oneCell.Address(True, True, xlA1) & "&"""""
               ' MsgBox strFormula
                'Exit Sub
                strFormula = "=AND(LEN(" & strAddress & ")>0," & strFormula & "   )"
                'MsgBox strFormula
                'Exit Sub
                With .FormatConditions
                    .Add Type:=xlExpression, Formula1:=strFormula
                    .Item(cfCell.FormatConditions.Count).SetFirstPriority
                    With .Item(1).Interior
                        .Color = oneCell.Interior.Color
                    End With
                End With
            Next oneCell
        End With
    Next cfCell
End Sub

 

[Kishan]

If you run this macro, it will set the conditional formatting conditions that you want.
You only have to run it once.
Thereafter, you can copy the formatting to any new cells.
If you change any values in the Fill List, F8:F16, the CF cond
If you change a color in the Fill List, or if you add any cells to the Fill List, you should run the macro again.

Sub test()
    Dim FillList As Range, rngCF As Range
    Dim oneCell As Range, cfCell As Range
    Dim strAddress As String, strFormula As String
    
    With Sheet1
        Set FillList = .Range("F8:F16"): Rem adjust
        Set rngCF = .Range("C:C")
    End With
    rngCF.FormatConditions.Delete
    Set rngCF = rngCF.SpecialCells(xlCellTypeConstants)

    For Each cfCell In rngCF
        With cfCell
            strAddress = .Address(False, False, xlA1)
            For Each oneCell In FillList
               
                strFormula = strAddress & "&"""" = " & oneCell.Address(True, True, xlA1) & "&"""""
               ' MsgBox strFormula
                'Exit Sub
                strFormula = "=AND(LEN(" & strAddress & ")>0," & strFormula & "   )"
                'MsgBox strFormula
                'Exit Sub
                With .FormatConditions
                    .Add Type:=xlExpression, Formula1:=strFormula
                    .Item(cfCell.FormatConditions.Count).SetFirstPriority
                    With .Item(1).Interior
                        .Color = oneCell.Interior.Color
                    End With
                End With
            Next oneCell
        End With
    Next cfCell
End Sub

Hi mikerickson, thank you for giving a VBA solution

After I run find it stops at the line below

 .Add Type:=xlExpression, Formula1:=strFormula

And produce the following error

Error ' 5 ' occurred at run time:
Invalid argument or call to procedure

I tried to goggle but for me it is been impossible to find the solution

Please could you check?

Thank you in advance

Regards,
Kishan