Control tool to change colored cell value?

**NickX** · 03-18-2011, 08:54 PM

Hello! I would like to know if it is possible to create a control or active x checkbox that when checked can change the value of any colored cell within a row to a numeric value? For example if cells A4, F4, H4 and S4 are all blank but colored yellow, is it possible to create a checkbox that when checked would change any yellow colored cell to equal 6? If that is not possible then I guess the same thing would be ok for the checkbox to change any cell that currently equals the letter X to instead change to 6 when the checkbox is clicked. Any help would be greatly greatly appreciated ! Thanks much

**MarvinP** · 03-18-2011, 09:45 PM

Hi NickX and welcome to the forum.

This function already exists in Excel. Do the following to test it.
Format A2, B3 and A6 with background color format of Yellow.
Now click on Ctrl-H to bring up the Find and Replace dialog.
Find a background of Yellow and replace with 6. Then click on Replace All.

Done.

**NickX** · 03-18-2011, 10:20 PM

Hi Marvin! Thanks- Would I be able to use the find and replace function in a control check box so when the box is checked all yellow cells in row A are replaced with the number 6?

**NickX** · 03-18-2011, 10:23 PM

Oh and if so then if the box is unchecked it would return to a blank yellow cell that would be great! Sorry I'm still a bit newer than I thought at Excel

**MarvinP** · 03-18-2011, 10:31 PM

You would need to write some VBA code to get a check box working. Then more code to trigger some event to change colors to numbers.

Try this. Go to the Macro Recorder and record a macro to change colors to some number. Then stop recording and look at the macro. This is the code you would need to do what you want.

Better would be to put a Quick Access Toolbar (QAT) Icon on the toolbar to change colors to numbers.

You need a lot of skill to get check boxes to do stuff in Excel and VBA.

**NickX** · 03-18-2011, 10:40 PM

Yeah I recorded a macro earlier where I simply highlighted the current yellow cells and changed the value of the cell to 6, stopped recording and then assigned it to the check box so when I checked it all yellow cells turned to 6. But I was looking more to have the check box automatically assign the yellow cells (or a cell with a value of the letter X) to the number 6 so that way in the future it could be different cells marked yellow that would automatically change