Conditional Formatting 4 Color Scale

[ndiaye]

Hello everyone,

I have data like this

12 23 35 44
45 6 3 9

I would like the show them:
highest one filling red
lowest one filling green
2nd highest one filling yellow
3nd highest one filling blue

is that possible? The question is shortly: IS 4 color scale conditional formatting possible in Excel?
Thank you.

 

[ndiaye]

I have found a solution,

Here i added the rules table. The important thing is you should not change the rows - which i mean is the placement must be same.

https://ibb.co/nnWbNhc

Hello everyone,

I have data like this

12 23 35 44
45 6 3 9

I would like the show them:
highest one filling red
lowest one filling green
2nd highest one filling yellow
3nd highest one filling blue

is that possible? The question is shortly: IS 4 color scale conditional formatting possible in Excel?
Thank you.

 

[Peter_SSs]

How particular are you about the specific colours? have you tried a 3-color scale and then edit the top, bottom & middle colours?

Do you always have 4 values? If so you can just set up 4 CF rules to do exactly what you want:

Excel Workbook
	A	B	C	D
1	12	23	35	44
2	45	6	3	9
CF 4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1	1. / Formula is =A1=LARGE($A1:$D1,4)	Abc
A1	2. / Formula is =A1=LARGE($A1:$D1,3)	Abc
A1	3. / Formula is =A1=LARGE($A1:$D1,2)	Abc
A1	4. / Formula is =A1=LARGE($A1:$D1,1)	Abc

 

[ndiaye]

As i said before,

I solved and i added a table. Users can also fix their problem with using your rep.

Thanks.

How particular are you about the specific colours? have you tried a 3-color scale and then edit the top, bottom & middle colours?

Do you always have 4 values? If so you can just set up 4 CF rules to do exactly what you want:

CF 4

	A	B	C	D

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:33px;"><col style="width:33px;"><col style="width:33px;"><col style="width:33px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92d050]#92d050[/URL] , align: right"]12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00b0f0]#00b0f0[/URL] , align: right"]23[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]35[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: right"]44[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] , align: right"]45[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00b0f0]#00b0f0[/URL] , align: right"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92d050]#92d050[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]9[/TD]

</tbody>

Conditional formatting

Cell Nr.: / Condition Format A1 1. / Formula is =A1=LARGE($A1:$D1,4) <tbody> [TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92d050]#92d050[/URL] "]Abc

<tbody>

A1	2. / Formula is =A1=LARGE($A1:$D1,3)
A1	3. / Formula is =A1=LARGE($A1:$D1,2)
A1	4. / Formula is =A1=LARGE($A1:$D1,1)

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00b0f0]#00b0f0[/URL] "]Abc[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "]Abc[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] "]Abc[/TD]

</tbody>

[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Peter_SSs]

As i said before,

I solved and i added a table.

Yep, but you only posted that 1 minute before I posted so I hadn't seen your post before I submitted mine.