Keep Conditional Formatting When Using Find and Replace

I have a work schedule that needs formatting. Ideally I would like to create and run a macro to make the necessary format changes. I've used conditional formatting to change the shading of the cell (whether the shift was a regular shift, overtime shift, vacation shift, sick shift, etc.) The cell also contains text that indicates whether the shift was a Day shift or Night Shift. I tried using Find and Replace to change the Day shift code to "D" and the night shift code to "N" but when I do this I loose all the conditional formatting and the color codes. Any suggestions on how to work around this?

Yes, that is what I mean, and I now understand that I am changing the conditional formatting I originally set and that is why I am loosing the color. Is there another way to do what I am trying to do? Attached is the spreadsheet with the cells shaded different colors and I would like to then be able to change them to "D" or "N" and delete all the other text but still keep the shaded cells.

I'm getting the impression that you mean you want the cells to stay the same color they are now. There will be only two new values, D and N, but you have 8 different colors. There is no conditional formatting rule that says, "Determine cell color based on what the content used to be."

If you want to preserve the colors, you will have to update the colors manually before changing the values in those cells. The most straightforward way to do this is to do a Find All for a value (such as Vacation), then use the results of the Find All to select all of the matching cells, then set the fill color from the Fill tool. That has to be repeated for each different value in your conditional formatting rules. It should take less than 5 minutes. Then you would do your find & replace.

The only other solution I can think of requires a moderate effort to write some VBA to do it. It would take a lot more than 5 minutes.