Copy conditional formatting without changing original format

Good morning everyone,

I have an odd question, I recently took a position at work that required me to take over some spreadsheets (scheduling gantts) from the former employee.

There is quite a bit of formatting in the spreadsheet that I would personally be fine without, but a lot of other eyes utilize these spreadsheets for various reasons so I would like to keep the changes I make relatively unnoticed to the ***** eye (really the 5 letter work for unclothed is blocked?). The schedule is based on the week number.

To make things easier for everyone I wanted to highlight a column based upon the current week. I placed the =today() function in a cell, then used the =weeknum in another to generate the week number. I applied conditional formatting to that if the top cell indicated it was week 43 and todays date fell in week 43 I would add a pattern fill to the cell.

How can I copy the conditional formatting rule over to the rest of the cells without losing the existing format? I basically want to merge the formats. Any ideas? Reapplying the existing format after applying the blanket conditional formatting wouldn't necessarily be difficult, just very time consuming (I have about 30 sheets in each workbook).

Any assistance is greatly appreciated.

Thank you,
Max

You should be able to just select all cells and add the conditional format to it.

It is just a matter of how you make your conditional formatting formula.

You can apply conditional formatting to multiple cells at the same time. You simply have to select them prior to adding the condtional formatting. The conditional formatting will not affect any formatting already in place. The conditional formatting will merly mask the original formatting in the event that the condition is TRUE.

The key to conditional formatting formulas is understanding fixed and relative references.

If you use the formula
=$A$1 = 1

Then all cells selected will change formats when A1 = 1.

But if you use
=$A1

Then all selected cells in row 1 will change formats if A1 = 1, but all selected cells in row 2 will only change when A2 = 1, and so on.

=A$1 is the same, only transposed to columns. Selected cells in column A will change if A1 = 1, but selected cells in B will change when B1 = 1.

=A1 - This will cause each cell to evaluate itself and if its value is 1, then it will change.

There is one special instruction, however. When you select cells, especially when you are selecting non-adjacent ranges, you must be aware what your "active" cell is. When you select a range of cells, they all turn a bluish color... except the active cell, which has a bold border and white background. Write your formula specific to that cell. If, for whatever reason, your active cell is C3, but you write your formula as if A1 were the starting cell, then wierd things may happen.

If you post a sample workbook, I can show you what I mean.

Hmm didn't work last friday, but it seems to be working today (probably was too brain dead after the work week).

In the conditional formating I had the following formula
=IF(BR$79=($A$79), 1, 0)>0

Thanks for your help!

Best regards,
Max