Delete data from a color formated cell and only keep coloring format

AVaes

New Member
Joined
Apr 17, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
I have a large file and need to conditional format the color, I understand how this works :). Now, I only want to keep the colouring, not the data. Is this possible?
When I delete the data in the colouring formatted cells, the color will dissapear too. How can i keep only the coloring format? I want to be able to put text in the colored cells afterwards....
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,633
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

Conditional Formatting is dynamic, and dependent on the data in the cell at that time. So if you remove the data, the Conditional Formatting will be automatically updated.
So with Conditional Formatting, you really cannot remove the data and leave the formatting.

You will most likely need to take a VBA approach. You may be able to use a Worksheet_Change event procedure VBA code, which is code that runs automatically when data is manually changed on your worksheet. So, in this VBA code, you would kind of recreate what the Conditional Formatting was doing (i.e. if certain conditions are met, color the cell a certain color). You could then have it do nothing/exit if the change to the cell is clearing its contents. Then it will leave what previous coloring the code had already applied to the cell.
 

AVaes

New Member
Joined
Apr 17, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Welcome to the Board!

Conditional Formatting is dynamic, and dependent on the data in the cell at that time. So if you remove the data, the Conditional Formatting will be automatically updated.
So with Conditional Formatting, you really cannot remove the data and leave the formatting.

You will most likely need to take a VBA approach. You may be able to use a Worksheet_Change event procedure VBA code, which is code that runs automatically when data is manually changed on your worksheet. So, in this VBA code, you would kind of recreate what the Conditional Formatting was doing (i.e. if certain conditions are met, color the cell a certain color). You could then have it do nothing/exit if the change to the cell is clearing its contents. Then it will leave what previous coloring the code had already applied to the cell.
Ok, I will look into the Worksheet_Change event procedure. Thx for the tip!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,633
Office Version
  1. 365
Platform
  1. Windows
Ok, I will look into the Worksheet_Change event procedure. Thx for the tip!
You are welcome.
Post back, if you need any help. Just be sure to include the details!
 

Watch MrExcel Video

Forum statistics

Threads
1,133,243
Messages
5,657,574
Members
418,401
Latest member
B_A_M155

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top