My question concerns conditional formatting in Excel. Here is a summary of my situation:
I have conditional formatting set up for a particular row in Sheet A. The formatting formula checks whether there is a value larger than zero in a different cell in Sheet B, and then changes the fill colour of the cell in Sheet A to yellow if there is indeed such a value. (To clarify - the colour of the cell does not have anything to do with the actual value of the cell itself, it changes the colour based on the value of a cell in a different sheet!)
This is the formula that has worked for me so far, when applied to individual cells: ='Sheet B'!O$70>0
Example of existing situation, formulas in italic for reference
So, if the conditional formatting using the formula above is applied to e.g. week 10, it works normally and will change the cell colour to yellow. However, if I want to apply the same formula to the cells for weeks 11 and 12, there is no way to do it by just selecting the whole range - then the formula stops working entirely. The same thing happens regardless of whether I use absolute or relative references. The only way to get it to work is to set the conditional formatting for each cell individually and manually update the formula (in this case, change O$70 to O$71 and so on, for each cell).
Doing this manually takes quite a lot of time - is there a way to do it faster? Ideally, this would be done through the Excel interface itself, rather than using VBA (although I am open to such options, if there is no other solution). I tried a number of tricks (e.g. using format painter or copying the formatting of the cell that works) - however, the problem with this is that it updates the column reference instead of the row reference, which is what I need. So, I would get formulas with P$70, Q$70, etc., instead of O$71, O$72...
So, to sum up, I need a way to automatically update the row reference when applying a conditional formatting formula across different cells. I hope this was clear enough and thank you for your help!
21 Answer
If the data on Sheet B is the same shape as the data you are trying to format, you ought to be able to just use the conditional format formula ='Sheet B'!O70>0. Note there are no $ signs. It should work either if you select all the data first, then apply that formula; or if you just apply that formula to the top-left cell, then use the Format Painter to copy it to the rest.
The result of this should mean that C2 depends on O70, D2 depends on P70, and so-on; and also that C3 depends on O71, D3 depends on P71, etc. If this isn't what you needed, then edit your example diagram to show what formulae you were expecting for the Site 2 row.
