Conditional Linked formatting

**phhsl** · 11-05-2010, 06:12 AM

I am searching for a means or formula in conditional formatting to link row's two and three of the attached example to L3.
So if L3 produces a result of 10, the formatting of B2 & C2 would be automatically bold, and C2 would have the same conditional colour scale formatting of B2.
Or if any of the range B3 to K3 match the result of L3 ( it can be 0 to10) , then B2 to K2 and B3 to K3's formatting would reflect that as bold and the same colour scale as B2 to K2.
I've tried and tested formulas and standard conditional formatting options to no avail.

Cheers

**6StringJazzer** · 11-05-2010, 01:36 PM

Originally Posted by phhsl

So if L3 produces a result of 10, the formatting of B2 & C2 would be automatically bold....

Or if any of the range B3 to K3 match the result of L3 ( it can be 0 to10) , then B2 to K2 and B3 to K3's formatting would reflect that as bold....

This won't be difficult but I don't understand your algorithm for determining what is bold. In your example, if L3 contains 10, that matches the value in B3. I can see why you might want to make B2 bold. Why is C2 also made bold?

Based on your second more general description, I can't tell what you want to be bold. Suppose F3 matches L3. Do you want only F2 to be bold, or B2:F2 to be bold?

As far as using the same format on the matching cells that is used above, this is more of a problem. The formatting you're using is the gradient, which is applied based on the content of the cell. There is no option for using a gradient by referring to a different cell. There is also no way (that I know of) to determine the current color of a cell when that color is caused by conditional formatting (either by using the CELL function or by using attributes in VBA). The only way I know that you could do this would be to use a series of conditional formatting formulas to explcitly set a series of colors, instead of using the gradient. Then you can replicate those formulas and colors in any cells you want.

Edit: It would also be helpful to understand how are you using this worksheet. What data is subject to change? There are no formulas; everything is a hard value so it's hard to envision how this will be used.

**phhsl** · 11-05-2010, 02:46 PM

Thanks very much Jazzer. I've been working more on resolving this from details in other posts and thought I had it solved by using a formula in conditional formatting of =OR(AND(A4<>K4)), replicating that at each cell while K4 remains constant, while also using the gradient colour conditional formatting. It works fine as detailed on the attached - if you change the number at K4 it brings forward the gradient colour on each cell that equals K4.
The problem I now have is that the 'score' detail I want to use at K8 is based on the formula =IF(M5<=A7,"10",IF(M5<=B7,"9",IF(M5<=C7,"8",IF(M5<=D7,"7",IF(M5<=E7,"6",IF(M5<=F7,"5",IF(M5<=G7,"4",IF(M5<=H7,"3",IF(M5<=J7,"2",IF(M5>=K7,"1"))))))))))
That formula automatically populates what the 'score' is, but the conditional formatting formula then doesn't work.
Without the formula, and with a simple number inserted at K4 , the conditional formatting works to deliver what I want.
With the formula, it doesn't work.
I've tried changing the number format of K8 but can't get it to recognise K8 in the formula =OR(AND(A8<>K8))
Is there something I can do to the IF formula to change the format?

Cheers

**6StringJazzer** · 11-05-2010, 07:27 PM

For me to help I need to know how you are using this worksheet. What data changes and what data stays the same? I see how you replicated the gradient on the second row but that assumes that those numbers are always in descending order, something that you haven't said. Your post here doesn't mention anything about bolded text, and your example doesn't do it, so maybe your dropped that.

Originally Posted by phhsl

... using a formula in conditional formatting of =OR(AND(A4<>K4)) ....

I don't understand what you are trying to do with that formula. AND and OR both take two or more arguments and you are only providing one, so that formula is exactly equivalent to

=A4<>K4

Also, if the first row of numbers is always strictly ascending, you can reverse the order to be ascending, eliminate the second row altogether, and use MATCH to get the score. See attached example, the third set of numbers I added.

**phhsl** · 11-06-2010, 12:05 PM

Thanks again Jazzer. The and/or formula I picked up yesterday and it worked but I see what’s needed is even more simple. Changing that still doesn’t allow K8 to be recognised in the conditional formatting though. Thanks for the MATCH formula too, which works great but won’t fit with how I intend this to be used.
To clarify how I intend this to work;
• Row 4’s 1 to 10 scores need to be conditionally formatted with the same colour as the range above as a visible indicator of where on the 1-10 scale the score sits. I originally wanted relevant cells in Row’s 3 & 4 to be bold font but the same conditional formatting colour works fine.
• Row 3 is a range of data which will change each time I replicate this scale but will always be in ascending order.
• Row 4 will always be a 1 to 10 scale but won't always ascend – in some instances the scale would descend from 10 to 1.
• Cell M5 will also vary each time.
Hope that helps?