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]

Hi mikerickson, changing the, with; pass the line

strFormula = "=AND(LEN(" & strAddress & ")>0," & strFormula & "   )"

[COLOR=#ff0000][B]To like this [/B][/COLOR][COLOR=#000000]strFormula = "=AND(LEN(" & strAddress & [/COLOR][COLOR=#ff0000][B]")>0[/B][/COLOR][COLOR=#ff0000][B];[/B][/COLOR][COLOR=#000000]" & strFormula & "   )"[/COLOR]

But now stuck on the line below

 .Add Type:=xlExpression, Formula1:=strFormula

Error ' 438 ' occurred at run time:


Please could you check?

Thank you in advance

Regards,
Kishan

 

[mikerickson]

It works for me. Might you have merged cells in column B?

 

[Kishan]

It works for me. Might you have merged cells in column B?

Hi mikerickson, thank you for your answer, yes I believe that you posted after it has worked for you.

May it is my version 2000 giving a problem and producing an error

I just open a new workbook rewrite all again but it the same problem continue I don't find the way to trace it

Please could you tell me how could can solve it?

Thank you

Regards,
Kishan

 

[Kishan]

Hi, Please if anyone is using excel 2000, can give a try the VBA code is in the post#9, I have broken my head but it stuck on line below. and cannot get it work.

 .Add Type:=xlExpression, Formula1:=strFormula

I do not know what I am doing wrong

Thank you in advance

Regards,
Kishan

 

[Kishan]

Bump

 

[Kishan]

Hi, 275 views seems everyone is updated latest versions

Is there any conditional formatting formula which and do the job will be fine too because it seems VBA post#9 is not compatible with my version

Thank you in advance

Regards,
Kishan

 

[Kishan]

Hi,

Spend hours goggling, and search entire MrExcel Message Board I am sure there are 100 of conditional format VBA and formula but such like mine, format after particular pattern and use format from the range define is not found.

The thing is that code is in the post#9 given by mikerickson is working for him correctly so he cannot make any alteration which is right, I think to get solve this if some one can have excel 2000 or can give here any other alternate code will be great full of him.

If I am getting "Run-time error '5' Invalid procedure call or argument." that mean I am doing something wrong, but what I don't know even I reinstall my excel again but not luck

Hope I might get some solution.

Thank you in advance

Regards,
Kishan

 

[Kishan]

Bump

 

[Kishan]

My Last Bump
May some one have got solution ?

Regards,
Kishan

 

[Kishan]

Thank you everyone, has looked this thread may my query is wrong will find another way to explain it in the new thread.

Regards,
Kishan