Conditional formatting for multiples of

[cra5hdown]

so we have a row like (row includes other higher values varying over hundreds of lines, but in essence these are what im after, its to do with overtime pay rates):

[TABLE="width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]1.5[/TD]
[TD]1.2[/TD]
[TD]1.8[/TD]
[TD]2[/TD]
[TD]2.4[/TD]
[TD]2.5[/TD]
[/TR]
</tbody>[/TABLE]

what i want to do is use conditional formatting to highlight as:

colour 1 (x1.5)
1.5 as it is 1.5x1
1.8 as it is 1.5x1.2

colour 2 (x2)
2 as it is 2x1
2.4 as it is 2x1.2

thanks in advance!

 

[Peter_SSs]

You could try this CF.

Excel Workbook
	A	B	C	D	E	F	G
2	1	1.5	1.2	1.8	2	2.4	2.5
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2	1. / Formula is =COUNTIF($A2:A2,B2/2)	Abc
B2	2. / Formula is =COUNTIF($A2:A2,B2/3*2)	Abc

One thing to consider: If it is possible with your data, what should happen if a cell meets both conditions? For example, with the numbers below, 3 is 2x1.5 but it is also 1.5x2

1.5, 2, 3

 

[cra5hdown]

Nice,

How about if this happened more than once each?

eg 1 & 1.5, 1.25 & 1.875, 1 & 2, 1.5 & 3?

while it is possbile that a value may meet both criteria, i believe it to be very unlikely in my situation.

also, another formatting that could be useful is the reverse, where colour 3 is for 1 & 1.25 & 1.5 (if OR ??)

thanks again

You could try this CF.

CF

	A	B	C	D	E	F	G

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"><col style="width:29px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffc000]#ffc000[/URL] , align: right"]1.5[/TD]
[TD="align: right"]1.2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffc000]#ffc000[/URL] , align: right"]1.8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92d050]#92d050[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92d050]#92d050[/URL] , align: right"]2.4[/TD]
[TD="align: right"]2.5[/TD]

</tbody>

Conditional formatting

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

<tbody>

B2	2. / Formula is =COUNTIF($A2:A2,B2/3*2)

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

</tbody>

[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


One thing to consider: If it is possible with your data, what should happen if a cell meets both conditions? For example, with the numbers below, 3 is 2x1.5 but it is also 1.5x2

1.5, 2, 3

 

[Peter_SSs]

How about if this happened more than once each?

eg 1 & 1.5, 1.25 & 1.875, 1 & 2, 1.5 & 3?

I don't understand. The existing example happened more than once each (2 green & 2 amber). In what way does the existing suggestion fail? Examples?

.. another formatting that could be useful is the reverse, where colour 3 is for 1 & 1.25 & 1.5 (if OR ??)

Again, that is not clear to me. Please give an example (or 2 or 3) of the values that could appear in a row and identify which one(s) should be coloured and why.

 

[cra5hdown]

Hmm,

I see from your example that it should work for for each x1.5 & x2, but for some reason on my sheet it only does it for the first one. ill play about with it.

the other part is largely unnecessary to be honest, but what I was saying that the '1's could do with being filled with a 3rd colour.

bear in mind im working with pay rates which are all different, and the 1 is actually x1, 1.5 =x1.5 the actual rate

there are erroneous rates around, the non filling of which would highlight

 

[Peter_SSs]

I see from your example that it should work for for each x1.5 & x2, but for some reason on my sheet it only does it for the first one.

For my example, you would first select from cell B2 down to the bottom right of the data and then apply the rules shown. Check carefully that you have places $ signs in the formulas where I have, and only where I have.