Conditional Formatting within same workbook but comparing different tabs

[ExcelUser18]

Hi All,

I need to compare two workbooks for a quarter over quarter analysis that are identical in size/formatting however some of the numbers may change. What I'd like to have is 1 workbook and have a 1Q21 tab and a 2Q21 tab side by side and apply a conditional formatting to 2Q21 which will show an increase (green) or a decrease (red) from the prior quarter. Of course if no change, no color would be applied. While the size (rows and columns) almost never change, it is possible that 3Q21 might be larger with an additional row or column (either in the middle or at the end of the range) than 2Q21, which may throw off the conditional formatting. For now, the range would be A1:AO150.

Also, if solved, would a simple copy tab to the same workbook continue to carry over the conditional formatting to the prior tab or how would you suggest?

Any help would be appreciated, please let me know if you need clarification.

 

[ExcelUser18]

I'm not sure if it's working correctly unless I typed something incorrectly (though I just copied and pasted). I pasted a mini-sheet of a section not previously sent. A few things to note:

1. I changed the colors just to have them stick out.
2. My sheet is actually much larger, right now its through row 119 and column AN. I made the formulas through AZ200 so as to capture future rows/columns.
3. The excel sheet is much slower now. Is that a function of the formula or the size of the sheet I'm looking to format?
4. The two sheets I'm using, 6.31.21 Test and 3.31.21 Final are exact replicas. I'm not sure why the data in Columns Y, Z, and AC are highlighting in Orange.
5. To clarify, the row will only turn orange IF the investment number is new?

 

[Toadstool]

I'm not sure if it's working correctly unless I typed something incorrectly (though I just copied and pasted). I pasted a mini-sheet of a section not previously sent. A few things to note:

1. I changed the colors just to have them stick out.
2. My sheet is actually much larger, right now its through row 119 and column AN. I made the formulas through AZ200 so as to capture future rows/columns.
3. The excel sheet is much slower now. Is that a function of the formula or the size of the sheet I'm looking to format?
4. The two sheets I'm using, 6.31.21 Test and 3.31.21 Final are exact replicas. I'm not sure why the data in Columns Y, Z, and AC are highlighting in Orange.
5. To clarify, the row will only turn orange IF the investment number is new?

1. OK
2. The formula was going up to column Q and row 999. You can change that range that by modifying one entry in each colour formula. Where it has $A$7:$Q$999 change to $A$7:$AZ$200 for checks up to column AZ and down to row 200.
3. Yes, INDIRECT is what is called a Volatile Function, meaning it executes when any cell is changed. The sheet will get slower as the range increases and if you have the Conditional Format on more sheets (which would suggest you have one sheet with these formulae called Comparison and just copy & paste in your data when you want to compare).
4. This is a function of answer 2. Make that change to the range and it should work.
5. The row will turn orange if the Investment Number is on the sheet with the Conditional Format (in your example 6.31.21 Test) but is not on the target sheet named in cell $D$1 (3.31.21 Final in your example). It cannot test for new Investment Numbers in sheet 3.31.21 Final.

 

[ExcelUser18]

Toadstool,

Thank you for all the very specific and direct information. Not surprisingly, this project was scrapped by my employer so this was all for naught. I appreciate all the feedback which speaks to the level of expertise in this forum/community.

Thanks again

 

[Toadstool]

Toadstool,

Thank you for all the very specific and direct information. Not surprisingly, this project was scrapped by my employer so this was all for naught. I appreciate all the feedback which speaks to the level of expertise in this forum/community.

Thanks again

You're welcome... but yes it is disappointing.