Help with existing pop up code

[Dazzawm]

I was given the codes within this thread http://www.mrexcel.com/forum/showthread.php?t=554868

Problem now is I have changed it from horizontal to vertical, so the names are going across the top and the dates down in column B. I need a pop up so that if someone has already an 'HP' in that date/row it will give a pop up saying 'such and such is already booked of that day' by looking at the name in row 3.

The names are ranging from C3:CB3 and the dates range from B4:B316

Thanks

 

[Dazzawm]

I have sorted that now I was being a ****. The depot ranges are C1 then the next starts at Q1 then AD1 then AP1 then BM1 and finally BW1. Can these ranges be added to your code?

Thanks MickG could you help me with this part please, and just one last thing please I dont need the word 'Depot' as a couple of ranges are sales and managers. Thanks.

 

[MickG]

Replace the similar line in your code with the line (in Red) below. (The word "Depot" has been removed)

Next Dn
             nMsg = nMsg & """" & Depot & """" & Chr(10) & Msg & Chr(10)                
Msg = ""
    Next nDn

 

[Dazzawm]

Sorry for the trouble and hassle but its not working correct. If enter HP in the range say starting in column S for an employee it doesnt tell me who is already off from an earlier range and sometimes people who are of in their own range, it also doesnt display the names of the depots etc that are in the cells I gave you. Just a bit of tweaking please!

 

[Dazzawm]

Would you like me to give you the range for each depot?

 

[MickG]

Can you post a small amout of data showing 2 to 3 "Depot" groups.

 

[Dazzawm]

Can you post a small amout of data showing 2 to 3 "Depot" groups.

I think it will be too big, do you want the ranges?

 

[Dazzawm]

Name of Depot C1 Range of names C4:P316
Name of Depot Q1 Range of names R4:AC316
Name of Depot AD1 Range of names AE4:AO316
Name of Depot AP1 Range of names AQ4:BL316
Name of Depot BM1 Range of names BN4:BV316
Name of Depot BW1 Range of names BX4:CB316

Thanks

 

[MickG]

The code works on the assumption that there is a blank column between each set of data.
From your ranges there seems to a problem.
You seem to show the ranges addresses for data entry within the main range of each group
Example:_First range:-
Name of Depot C1 , this is the 3rd column in within the first group
Range of names C4:P316. i.e col "C" to col "P"
Now for the next Group to have its name in 3rd column of the group and also have a blank column in between, the name would need to be in cell "T" i.e.Col "P" last column ,"Q" Blank, "R & S" start of the 2nd Group and T the postion for new Name.
NB:- The position of the "Name" must be consistent within each Group

NB:- Similar problem below :-
Name of Depot Q1 Range of names R4:AC316
Name of Depot AD1 Range of names AE4:AO316
Name of Depot AP1 Range of names AQ4:BL316
Name of Depot BM1 Range of names BN4:BV316
Name of Depot BW1 Range of names BX4:CB316

Please try and show a couple of groups which hopefully will be representative of all the groups. This will help me get an understanding of your layout.

 

[Dazzawm]

I do have an empty column betwenn each depot to seperate here is a portion taken out of the file.

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="width:30px; " /><col style="width:80px;" /><col style="width:75px;" /><col style="width:79px;" /><col style="width:102px;" /><col style="width:83px;" /><col style="width:79px;" /><col style="width:93px;" /><col style="width:99px;" /><col style="width:78px;" /><col style="width:87px;" /><col style="width:104px;" /><col style="width:89px;" /><col style="width:115px;" /><col style="width:70px;" /><col style="width:90px;" /><col style="width:110px;" /><col style="width:49px;" /><col style="width:100px;" /><col style="width:92px;" /><col style="width:103px;" /><col style="width:85px;" /><col style="width:100px;" /><col style="width:85px;" /><col style="width:94px;" /><col style="width:76px;" /><col style="width:77px;" /><col style="width:79px;" /><col style="width:122px;" /><col style="width:106px;" /><col style="width:57px;" /></colgroup><tr style="background-color:#cacaca; text-align:center;font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >N</td><td >O</td><td >P</td><td >Q</td><td >R</td><td >S</td><td >T</td><td >U</td><td >V</td><td >W</td><td >X</td><td >Y</td><td >Z</td><td >AA</td><td >AB</td><td >AC</td><td >AD</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#33cccc; font-weight:bold; text-align:left; ">RD</td><td style="background-color:#ffcc99; font-weight:bold; text-align:left; ">HP</td><td style="font-weight:bold; text-align:left; ">Depot 1</td><td style="font-weight:bold; "> </td><td style="font-weight:bold; "> </td><td style="font-weight:bold; "> </td><td style="font-weight:bold; "> </td><td style="font-weight:bold; "> </td><td style="font-weight:bold; "> </td><td style="font-weight:bold; "> </td><td style="font-weight:bold; "> </td><td style="font-weight:bold; "> </td><td style="font-weight:bold; "> </td><td style="font-weight:bold; "> </td><td style="font-weight:bold; "> </td><td style="font-weight:bold; "> </td><td style="font-weight:bold; text-align:left; ">Depot 2</td><td style="font-weight:bold; "> </td><td style="font-weight:bold; "> </td><td style="font-weight:bold; "> </td><td style="font-weight:bold; "> </td><td style="font-weight:bold; "> </td><td style="font-weight:bold; "> </td><td style="font-weight:bold; "> </td><td style="font-weight:bold; "> </td><td style="font-weight:bold; "> </td><td style="font-weight:bold; "> </td><td style="font-weight:bold; "> </td><td style="font-weight:bold; "> </td><td style="font-weight:bold; text-align:left; ">Depot 3</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#ffff99; font-weight:bold; text-align:left; ">MP</td><td style="background-color:#ccffcc; font-weight:bold; text-align:left; ">SU</td><td style="background-color:#ffff00; font-weight:bold; text-align:left; ">Manager</td><td style="background-color:#ffff00; font-weight:bold; text-align:left; ">Asst Manager</td><td style="background-color:#ffff00; font-weight:bold; text-align:left; ">Warehouse</td><td style="background-color:#ffff00; font-weight:bold; text-align:left; ">Warehouse</td><td style="background-color:#ffff00; font-weight:bold; text-align:left; ">Driver</td><td style="background-color:#ffff00; font-weight:bold; text-align:left; ">Driver</td><td style="background-color:#ffff00; font-weight:bold; text-align:left; ">Driver</td><td style="background-color:#ffff00; font-weight:bold; text-align:left; ">Driver</td><td style="background-color:#ffff00; font-weight:bold; text-align:left; ">Driver</td><td style="background-color:#ffff00; font-weight:bold; text-align:left; ">Driver</td><td style="background-color:#ffff00; font-weight:bold; text-align:left; ">Driver</td><td style="background-color:#ffff00; font-weight:bold; text-align:left; ">Driver</td><td style="background-color:#ffff00; font-weight:bold; text-align:left; ">Biker</td><td style="background-color:#ffff00; font-weight:bold; text-align:left; ">Biker</td><td style="background-color:#99cc00; font-weight:bold; "> </td><td style="background-color:#ffff00; font-weight:bold; text-align:left; ">Manager</td><td style="background-color:#ffff00; font-weight:bold; text-align:left; ">Asst Manager</td><td style="background-color:#ffff00; font-weight:bold; text-align:left; ">Warehouse</td><td style="background-color:#ffff00; font-weight:bold; text-align:left; ">Warehouse</td><td style="background-color:#ffff00; font-weight:bold; text-align:left; ">Driver</td><td style="background-color:#ffff00; font-weight:bold; text-align:left; ">Driver</td><td style="background-color:#ffff00; font-weight:bold; text-align:left; ">Driver</td><td style="background-color:#ffff00; font-weight:bold; text-align:left; ">Driver</td><td style="background-color:#ffff00; font-weight:bold; text-align:left; ">Driver</td><td style="background-color:#ffff00; font-weight:bold; text-align:left; ">Biker</td><td style="background-color:#ffff00; font-weight:bold; text-align:left; ">Biker</td><td style="background-color:#ffff00; font-weight:bold; text-align:left; ">Biker</td><td style="background-color:#99cc00; "> </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="background-color:#99ccff; font-weight:bold; text-align:left; ">BH</td><td style="background-color:#99cc00; font-weight:bold; text-align:left; ">HU</td><td style="font-weight:bold; text-align:left; ">Tom</td><td style="font-weight:bold; text-align:left; ">Chris </td><td style="font-weight:bold; text-align:left; ">Chris T</td><td style="font-weight:bold; text-align:left; ">James </td><td style="font-weight:bold; text-align:left; ">Curtis C</td><td style="font-weight:bold; text-align:left; ">Alan </td><td style="font-weight:bold; text-align:left; ">John </td><td style="font-weight:bold; text-align:left; ">Leslie </td><td style="font-weight:bold; text-align:left; ">Michael </td><td style="font-weight:bold; text-align:left; ">Mike </td><td style="font-weight:bold; text-align:left; ">Roger </td><td style="font-weight:bold; text-align:left; ">Steve </td><td style="font-weight:bold; text-align:left; ">Diego </td><td style="font-weight:bold; text-align:left; ">Wenderson </td><td style="background-color:#99cc00; font-weight:bold; "> </td><td style="font-weight:bold; text-align:left; ">Kevin </td><td style="font-weight:bold; text-align:left; ">Mark </td><td style="font-weight:bold; text-align:left; ">Graham </td><td style="font-weight:bold; text-align:left; ">Nathan </td><td style="font-weight:bold; text-align:left; ">James </td><td style="font-weight:bold; text-align:left; ">John</td><td style="font-weight:bold; text-align:left; ">Brian </td><td style="font-weight:bold; text-align:left; ">Tony </td><td style="font-weight:bold; text-align:left; ">Ian H</td><td style="font-weight:bold; text-align:left; ">Alex </td><td style="font-weight:bold; text-align:left; ">Andre </td><td style="font-weight:bold; text-align:left; ">Gabriel </td><td style="background-color:#99cc00; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:left; ">Monday</td><td style="font-weight:bold; text-align:left; ">02/01/2012</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#99cc00; "> </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#99cc00; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:left; ">Tuesday</td><td style="font-weight:bold; text-align:left; ">03/01/2012</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#99cc00; "> </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#99cc00; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:left; ">Wednesday</td><td style="font-weight:bold; text-align:left; ">04/01/2012</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#99cc00; "> </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#99cc00; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:left; ">Thursday</td><td style="font-weight:bold; text-align:left; ">05/01/2012</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#99cc00; "> </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#99cc00; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:left; ">Friday</td><td style="font-weight:bold; text-align:left; ">06/01/2012</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#99cc00; "> </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#99cc00; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="background-color:#c0c0c0; text-align:left; ">Saturday</td><td style="background-color:#c0c0c0; font-weight:bold; text-align:left; ">07/01/2012</td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:left; ">Monday</td><td style="font-weight:bold; text-align:left; ">09/01/2012</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#99cc00; "> </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#99cc00; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:left; ">Tuesday</td><td style="font-weight:bold; text-align:left; ">10/01/2012</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#99cc00; "> </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#99cc00; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:left; ">Wednesday</td><td style="font-weight:bold; text-align:left; ">11/01/2012</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#99cc00; "> </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#99cc00; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:left; ">Thursday</td><td style="font-weight:bold; text-align:left; ">12/01/2012</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#99cc00; "> </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#99cc00; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:left; ">Friday</td><td style="font-weight:bold; text-align:left; ">13/01/2012</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#99cc00; "> </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#99cc00; "> </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="background-color:#c0c0c0; text-align:left; ">Saturday</td><td style="background-color:#c0c0c0; font-weight:bold; text-align:left; ">14/01/2012</td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td><td style="background-color:#c0c0c0; "> </td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href

 

[MickG]

Try this:-

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] nDn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Msg [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] nMsg [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Depot [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Fd [COLOR="Navy"]As[/COLOR] Boolean
[COLOR="Navy"]Dim[/COLOR] nFd [COLOR="Navy"]As[/COLOR] Boolean
[COLOR="Navy"]If[/COLOR] Target.Count = 1 And Not Intersect(Target, Range("A1:CB316")) [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("C3"), Cells(3, Columns.Count).End(xlToLeft))
[COLOR="Navy"]Set[/COLOR] Rng = Rng.SpecialCells(xlCellTypeConstants)
    
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng.Areas
        [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]Set[/COLOR] nRng = Dn.Offset(Target.Row - 3)
       [COLOR="Navy"]Else[/COLOR]
            [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn.Offset(Target.Row - 3))
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] nDn [COLOR="Navy"]In[/COLOR] nRng.Areas
   [COLOR="Navy"]If[/COLOR] Not Intersect(Target, nDn) [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] Fd = True
[COLOR="Navy"]Next[/COLOR] nDn
[COLOR="Navy"]If[/COLOR] Fd [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] nDn [COLOR="Navy"]In[/COLOR] nRng.Areas
      Depot = IIf(nDn(1).Column = 3, nDn(1).Offset(-Target.Row + 1), nDn(1).Offset(-Target.Row + 1, -1))
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] nDn
                [COLOR="Navy"]If[/COLOR] Target = "HP" And Dn <> "" And Not Dn.Address = Target.Address [COLOR="Navy"]Then[/COLOR]
                    Msg = Msg & Cells(3, Dn.Column) & Chr(10)
                 nFd = True
                [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]Next[/COLOR] Dn
                nMsg = nMsg & """" & Depot & """" & Chr(10) & Msg & Chr(10)
                Msg = ""
    [COLOR="Navy"]Next[/COLOR] nDn
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]If[/COLOR] nFd And Target = "HP" [COLOR="Navy"]Then[/COLOR]
    MsgBox "The following Staff are off today " & Chr(10) & Chr(10) & nMsg & Chr(10) & "Press ""OK"" to continue !!"
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Dim[/COLOR] intclr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
 
[COLOR="Navy"]If[/COLOR] Target.Count > 1 [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]
 
 
[COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] Target
[COLOR="Navy"]Case[/COLOR] "HP": intclr = 40
[COLOR="Navy"]Case[/COLOR] "MP": intclr = 36
[COLOR="Navy"]Case[/COLOR] "SU": intclr = 35
[COLOR="Navy"]Case[/COLOR] "BH": intclr = 37
[COLOR="Navy"]Case[/COLOR] "HU": intclr = 43
[COLOR="Navy"]Case[/COLOR] "RD": intclr = 42
[COLOR="Navy"]Case[/COLOR] "": intclr = xlNone
[COLOR="Navy"]Case[/COLOR] [COLOR="Navy"]Else[/COLOR]
[COLOR="Navy"]End[/COLOR] Select
 
Target.Interior.ColorIndex = intclr
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick