Apply Conditional Formatting to Subtotal

[Emeng]

Hi all
I'm trying to apply conditional formatting to a subtotal if it's value is greater than another cell's which is identified using Index/Match.
I'm working thru the steps methodically, and have reached the point where I can compare one cell with another. I have tried 5 different methods but can't crack the code to make the changes.

See the attached spreadsheet for my efforts so far.

In future I would like to use VBA to apply a formula over several sheets where the sheetname is the same as the lookup range.

Any help is much appreciated.

Thanks & regards

Mark

 

[bobhc]

How about pointing out which cells/column you want CF applied to. To which cells are the subtotals applied?


.

 

[Emeng]

(note to self: Save before Posting)

Hi Bob

Thanks for your suggestion.
I have added the CF dialog box to show workings so far.

Results in Column"P" show that the formula returns the desired result, but does not when applied to CF.

This spreadsheet for solving this problem only. The result will be used in another larger workbook where the Index/Match data is in a separate sheet which other sheets will refer to.... otherwise I would refer to the cell directly.

Thanks & regards

Mark

 

[NARAYANK991]

Hi Mark ,

I am very confused by the CF rules you have put in place.

Can you , in simple words , explain how you want the cells in column K to be coloured ?

1. How many different colours will you be using ?

2. What will be the logic under which a colour will be applied to a cell ?

Narayan

 

[Emeng]

Hi Narayan

Thanks for your interest

This spreadsheet is a mock-up of part of my project, which for clarity I won’t go into here. A bite sized chunk you might say.

Essentially I have so far tried 5 different methods to change font & fill

1. to same as K1,
2. if K2>L2.

Looking at the screenshot of CF dialog box, I have tried them in order from bottom to top.

Cell P2 = ”=IF(AND(ISNUMBER(SEARCH("Total",J2)),K2>INDEX(Erect,MATCH(I2,Dates))),"x","1")” & is the True/False switch I thought would work for CF (without the IF component). It returns the result I expect on the s/sheet but does not transfer to CF.

(I'm using I/M because my project will have the Dates/Ropes/Erect data on a separate sheet that this and another 9 sheets will refer to.)

OK, I thought that hasn’t worked, what about P2 = x, or P2=”x”, (P2)=”x” and so on, but no result.

Using the inbuilt cell value reference 'Cell Value > "L2" ' or directly referencing cells does not trigger the changes I’m looking for.

I am confused why none of them works... I thought I might accidentally get one of them right!

If you’re able to shed any light would be much appreciated.

Thanks & regards

Mark

 

[NARAYANK991]

Hi Mark ,

My earlier questions remain unanswered. I will just guess that what you are looking for can be obtained as follows :

1. Select the data range K2:K8

2. Clear all rules already in place , and enter the following formula as the CF rule :

=$K2>$L2

Narayan

 

[Emeng]

Hi Narayan

Thanks for the solution, you've come thru again!

Sorry for any confusion. With your questions 1. & 2., I thought to answer in the same format.

I find CF is a space with endless opportunity for error.

Thanks & regards

Mark