Creating a cell in colour

**helpme123** · 04-20-2009, 05:09 PM

good evening
i have an excel sheet which i want to enter a number in cell say b25 and if the number shows on the sheet between cell c3 and h23 how is this done, all it is for is simple lottery sheet number 1-49 entered into b25 if some one hase the number say in c3 then it turns red? thanks in advance for any help

**GuruWannaB** · 04-20-2009, 05:11 PM

Do you want C3 to turn red if its the number in b25? or do you want b25 to turn red if its in your c3:h23 range?

**helpme123** · 04-20-2009, 05:25 PM

yes if i enter number 14 in c25 and the number appears in lets say d12 f14 and e17 then i want those three to turn red. thanks for the speey responce

**GuruWannaB** · 04-20-2009, 05:29 PM

Look at this...I used conditional formats and named ranges.

Hope this helps,

GWB

**helpme123** · 04-20-2009, 05:59 PM

thanks for that that was just the ticket, the only problem it does not save the numbers entered and stay red if you change the number ?

**GuruWannaB** · 04-20-2009, 06:35 PM

I'm not the best with VBA...but I was able to write something that works.

Please Login or Register  to view this content.

I have this as a selection change event (Right click on the tab and select view code...paste it in there) I've also included the updated example sheet.

**GuruWannaB** · 04-20-2009, 06:46 PM

Sorry...just realized I missed something on the example sheet....to clear the existing color formats from numbers already put in previously...you would need to highlight those cells and simply click on the background format button and select NO FILL. That will start you off with a blank formatted data range - which you could record a macro to that process and tag it to a button for easiness if you wanted.

**helpme123** · 04-21-2009, 02:46 PM

SORRY TO KEEP GOING ON
i tried and tried i have a spreadsheet like the one you attached with 49 different numbers on, it is for the bonus ball. if i enter this weeks bonus ball
(37) in b25 and it appears in 4 seperate cells i want those cells to turn red. then on wednesday i enter bonus ball numer (4) in b25 and it appears in seperate cells i want those to turn red but keeping the previous numbers coloured red.

**GuruWannaB** · 04-21-2009, 05:17 PM

Originally Posted by helpme123

SORRY TO KEEP GOING ON
i tried and tried i have a spreadsheet like the one you attached with 49 different numbers on, it is for the bonus ball. if i enter this weeks bonus ball
(37) in b25 and it appears in 4 seperate cells i want those cells to turn red. then on wednesday i enter bonus ball numer (4) in b25 and it appears in seperate cells i want those to turn red but keeping the previous numbers coloured red.

The last project I uploaded does this...you can put any numbers in the data range...and any value you put in b25 will be sought out for and the background updated...if you change the number, the background colors will stay for the previous number(s) entered and it will seek out the new number you enter.