VBA Reference problem

[PENGUIN]

Hi Guys,

My first post so big welcome.

I have been using your tips for quite long and normally getting what I needed using the newbie 'step-by-step' and self learn practise but now I'm stuck.

I'm not too good with programing in VBA for my own, more like recording and stuff I'm familiar with so please be gentle

The problem I'm having is here:
Having this code what does work etc I'm willing to use another sheet (tab) from the same workbook to refer to (instead 'G2' I need tab colled 'Personel' and G2 from there).

Above is an first step only. Now I need to refer to range of cells, not to only one of them and I believe the code is wrong to do it so.
I would name it something like that:
If Cell from my range (B4:C9) contains one of from G1:G10 (located at 'Personel' tab) then Cell.Interior.ColorIndex = 3.

Basically there are 8 groups of personel with 10 names in each of them and I have a nice list created, now I would like to refer to the list while filling they payslips using the names. And if for eg. gary works for IT and I have typed his name it's blue, etc[B].

Private Sub Worksheet_Change(ByVal Target As Range)
Set MyPlage = Range("B4:C9")
    For Each Cell In MyPlage

        If Cell.Value = Range("G2") Then
            Cell.Interior.ColorIndex = 3
        End If

    Next
End Sub

[Aussiebear]

If cel.value = $G$2.value then

Try not to use any of excel's reserved words(cell) in your variable names.

[PENGUIN]

Hi,
Thanks for the answer but this still doeas the same as my code.
I need to refer to other sheet in the same workbook and to range of cells (G1:G10) then 1 cell only.


I will have 8 referentials in total while conditional formating allows only 3 for the sheet.


If Cell.Value = Range("G2") Then
Cell.Interior.ColorIndex = 3
End If


and other


If Cell.Value = Range("G2") Then
Cell.Interior.ColorIndex = 3
End If


etc up to 8 or more.


Sorry, forgot to mention!

[GTO]

I would suggest putting together a sample workbook and attaching it. Explain in plain English what we are wanting to do.

Hope thta helps,

Mark

[Bob Phillips]

Maybe

If Cell.Value = Worksheets("tab").Range("G2") Then