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.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim numbers As Range
Dim xvz As Integer

Set numbers = Range("C3:h23")

xvz = Range("b25").Value
    For Each cell In numbers
        If cell.Value = xvz Then
            With cell.Interior
                .ColorIndex = 3
                .Pattern = xlSolid
            End With
        End If
    Next cell
End Sub

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.