I have a set of values each with different coloured backgrounds in one coloumn that i want to copy and paste over to another column, but when i delete the values after copying and pasting them, the coloured background stays. I want to know if there is way to apply conditional formatting to empty cells so that they can be changed to white, non transparent, to replace the coloured background (or something with the same effect). I tried doing it with “contains” then “”, and then a custom style, but when i opened the condition menu up again it just went back to the default style and kept the background colour. I tried “Contains” then “”, which didn’t work, then “Not Contains” then put in all the possible values with coloured backgrounds in perenthesies which didnt work. anyone got any ideas?
For example to test if cell F1 is empty,
- Open the Conditional Format dialog,
- Set the condition to : “Formula is”
- Set the formula to : “ISBLANK(F1)”
And how do you make this work for a range of cells?
3a) Set the formula to "ISBLANK($Sheet1.$A1), or whatever is the top-left-most cell in the range of cells. $ signs indicate the portions of the cell identifier that do not change as the formatting algorithm applies the conditional formatting throughout the workbook. In this case, the worksheet name and the column letter stay the same (hence the $ signs) while the row number is variable, because the hypothetical range that I’m using in my example consists of many rows in a single column on a single worksheet.