Fill define colours

K

Kishan

Well-known Member
Joined
Mar 15, 2011
Messages
1,648
Office Version
  1. 2010
Platform
  1. Windows
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
ABCDEFG
1
2
3
4
5
63-XFill These
7ResultsColours
81-X0
901-1
101-11-X
112-11-2
123-X2-1
133-X2-X
142-22-2
152-X3-X
161-13-2
171-X
182-1
193-X
202-2
212-X
221-1
231-1
242-1
253-X
262-X
271-1
282-2
292-1
303-X
313-2
320
332-X
342-1
353-X
362-2
371-X
380
393-X
403-X
413-X
423-X
433-X
442-X
451-2
460
470
483-X
491-1
501-1
512-X
522-1
533-X
541-X
552-2
562-1
573-X
580
591-1
602-1
613-X
623-X
632-X
642-2
652-1
663-X
673-X
683-X
691-X
702-X
71
72
Sheet1


Thank you in advance

Regards,
Kishan
 
Last edited:
Sort by date Sort by votes
Kishan said:
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
Click to expand...

Hi!

Maybe the formulas in the table below can helps (in Conditional Formatting).

[TABLE="class: grid, width: 1154"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]Q[/TD]
[TD="align: center"]R[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Yellow[/TD]
[TD="align: center"]Red[/TD]
[TD="align: center"]Blue[/TD]
[TD="align: center"]Green[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"][/TD]
[TD="align: center"]=IF(C8=$F$16,IF(C7=$C$6,1,0),IF(C8=$C$6,1,0))[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]=IF(OR(C8=$F$12:$F$14),IF(C7=$C$6,1,0),0)[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]=IF(OR(C8=$F$9:$F$11),IF(C7=$C$6,1,0),0)[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]=IF(C8=$F$8,IF(C7=$C$6,1,0),0)[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]***[/TD]
[TD="align: center"]**[/TD]
[TD="align: center"]*******************************[/TD]
[TD]
*******************************​
[/TD]
[TD]
*******************************​
[/TD]
[TD]
*******************​
[/TD]
[TD="align: center"]**[/TD]
[/TR]
</tbody>[/TABLE]


PS: the problem is that you have only 3 options in Excel 2000.

Markmzz
 
Upvote 0

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
markmzz said:
Hi!

Maybe the formulas in the table below can helps (in Conditional Formatting).

[TABLE="class: grid, width: 1154"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]M
[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]O
[/TD]
[TD="align: center"]P
[/TD]
[TD="align: center"]Q
[/TD]
[TD="align: center"]R
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Yellow
[/TD]
[TD="align: center"]Red
[/TD]
[TD="align: center"]Blue
[/TD]
[TD="align: center"]Green
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]=IF(C8=$F$16,IF(C7=$C$6,1,0),IF(C8=$C$6,1,0))
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]=IF(OR(C8=$F$12:$F$14),IF(C7=$C$6,1,0),0)
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]=IF(OR(C8=$F$9:$F$11),IF(C7=$C$6,1,0),0)
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]11
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]=IF(C8=$F$8,IF(C7=$C$6,1,0),0)
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]12
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]***
[/TD]
[TD="align: center"]**
[/TD]
[TD="align: center"]*******************************
[/TD]
[TD]
*******************************​
[/TD]
[TD]
*******************************​
[/TD]
[TD]
*******************​
[/TD]
[TD="align: center"]**
[/TD]
[/TR]
</tbody>[/TABLE]


PS: the problem is that you have only 3 options in Excel 2000.

Markmzz
Click to expand...
Hi markmzz, this is amazing!! Conditional Formatting Formulas is giving expected result as I wanted. :) As you said version 2000 support only 3 conditions does not matter, fourth condition where is 0 has not much importance, what you provide it is great help to me.

I appreciate your help with my heart.

Have a nice weekend

Good Luck

Kind Regards,
Kishan :)

 
Last edited:
Upvote 0
Kishan said:
Hi markmzz, this is amazing!! Conditional Formatting Formulas is giving expected result as I wanted. :) As you said version 2000 support only 3 conditions does not matter, fourth condition where is 0 has not much importance, what you provide it is great help to me.

I appreciate your help with my heart.

Have a nice weekend

Good Luck

Kind Regards,
Kishan :)
Click to expand...

Hi Kishan,

I'm glad to help and thank you for the feedback.

Markmzz
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Especial fill cells colouring as per predefined number of rows
Replies
9
Views
797
MickG
M

Forum statistics

Threads
1,224,827
Messages
6,181,200
Members
453,022
Latest member
RobertV1609

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top