Thanks for your inputs have done them.
One last thing i got to ask
When i select "All" from the dropdown, the columns appear in teh form
entered , randomnly.
Is there a way that when "all is selected" i get the Teams ordered(sorted)
like all CTS,then all DBA, ...
Thanks again
Max

"p45cal" wrote:


max;511100 Wrote:
Dear P45cal,
Amazing!!!!!!!!!!!!!!!!! I have no words to explain your expertise,
thanks
a lot.
Well that part is working fine now.
I just have to achieve one more thing. From D10toR39 as in this sheet.
I
have actually the shift data. Well i have 2 questions here.
1 When user chooses from Dropdown "vacation,Off Day,Holiday the cell
colr shold be white. When General i want one color, when ITOps-2ndshift
i
want cell to change color when selected...

For this you need to use the 'formula is' aspect of Conditional
formatting for the cells; say you're in F10, you could put formulae in
each of the three conditions (xl2003) such as:

=OR(F10="Off Day",F10="Vacation",F10="Holiday")
=F10="ITOps-2ndShift"
=F10="General"

and put whatever colours/formatting you want to use in each case. Then
you can copy that conditional formatting to other cells.

max;511100 Wrote:
2 i tried placing a text box on the top near D1 to G1 to explain the
time
Like General: 08:00 - 17:00 "Color"
but due to freeze panes that will not be visile to user i want it to
stay there regardless of we move columns. How can i do this or any
other way
to show that details in the same sheet. Please suggest
Thanks again for your time
Max

"p45cal" wrote:


This is a macro solution I'm afraid.

In the Properties tab of the Format Text Box dialogue box, choose
'Don't move or size with cells'. The box will remain in place, visible,
regardless of which columns are hidden.


I've streamlined the code I posted before, especially the bit that
creates a list for the validation dropdown (it did too much looping for
my liking) as well as removing some now redundant lines and moving one
or two others.:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 8 Then
'This section keeps the Data Validation up to date if the depts. in
row 8 are added to or changed:
Set DeptsRng = Intersect(Range("D8").CurrentRegion, Rows(8),
Range("D:DP"))
Set uniquelist = CreateObject("Scripting.Dictionary")
For Each k In DeptsRng.Value
If Not uniquelist.exists(k) Then
uniquelist.Add k, k
DropDownStr = DropDownStr & k & ","
End If
Next k
DropDownStr = DropDownStr & "All"
With Range("B2").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=
_
xlBetween, Formula1:=DropDownStr
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
End If
'this bit hides/shows columns:
If Target.Address = "$B$2" Then
Set DeptsRng = Intersect(Range("D8").CurrentRegion, Rows(8),
Range("D:DP"))
If Range("B2") = "All" Then
DeptsRng.EntireColumn.Hidden = False
Else
DeptsRng.EntireColumn.Hidden = True
For Each cll In DeptsRng.Cells
If cll.Value = Range("B2").Value Then cll.EntireColumn.Hidden =
False
Next cll
End If
End If
End Sub


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=140062