Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Choosing offset on condition found in combobox change colour of cell??
I have the code below, it's kind of patched together, it works as far as finding the date in one of the worksheets in the array (the message box proves this) but then after finding the date it should choose an offset depending on the value in combobox1 and then colour it red however this is where the problem is..........i also want to be able to change the cell back to its original colour after the date in combobox2 searched has passed by 7 days. Any ideas? Regards, Simon Sub staffdates() Dim wks As Worksheet Dim rng As Range Dim strFirst As String Dim arr As Variant Dim t1 As Variant Dim r As Range Dim mycell dv = ComboBox2.Text sn = ComboBox1.Text arr = Array("Week1", "Week2", "Week3", "Week4", "Week5", _ "Week6") 'On Error GoTo XIT Application.EnableEvents = False For Each wks In Worksheets(arr) wks.Visible = xlSheetVisible Set rng = wks.Range("A1:A300") For Each mycell In rng If mycell.Text = dv Then MsgBox "found " & mycell.Text With Worksheets(arr) Select Case sn Case Is = "Lauren" Set r = mycell.Offset(1, 1) Case Is = "Emma" Set r = mycell.Offset(1, 2) Case Is = "Cheryl" Set r = mycell.Offset(1, 3) End Select End With Selection = mycell With Sheets(wks.Name) Selection.Interior ColorIndex = 3 Pattern = xlSolid PatternColorIndex = xlAutomatic End With End If Next mycell Exit Sub wks.Visible = xlSheetHidden Next wks XIT: Application.EnableEvents = True Worksheets("Week Selection").Visible = True Unload Me End Sub Private Sub ComboBox2_Change() ComboBox2 = Format(ComboBox2.Value, "dd mmmm yyyy") End Sub Private Sub CommandButton1_Click() Call staffdates End Sub -- Simon Lloyd ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708 View this thread: http://www.excelforum.com/showthread...hreadid=557873 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Choosing offset on condition found in combobox change colour of cell??
This post is on from a previous post http://www.excelforum.com/showthread.php?t=557701, i have tried various combinations after the Case element but i cannot seem to select or use "r" and if i dont the code below stops at the With Sheets(wks.Name). Any ideas? Regards Simon -- Simon Lloyd ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708 View this thread: http://www.excelforum.com/showthread...hreadid=557873 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Choosing offset on condition found in combobox change colour of cell??
Hi all i have modified the code below........and indeed it does colour a cell Red after finding the date in the rng.....however the cell it is colouring red is the last clicked cell (activecell) on the sheet!, not only is it colouring it red it is filling it with the contents of "r" (mycell.offset(x, x)) any ideas why this is happening? Regards, Simon Sub staffdates() Dim wks As Worksheet Dim rng As Range Dim strFirst As String Dim arr As Variant Dim t1 As Variant Dim r As Range Dim mycell dv = ComboBox2.Text sn = ComboBox1.Text arr = Array("Week1", "Week2", "Week3", "Week4", "Week5", _ "Week6") 'On Error GoTo XIT Application.EnableEvents = False For Each wks In Worksheets(arr) wks.Visible = xlSheetVisible Set rng = wks.Range("A1:A300") For Each mycell In rng If mycell.Text = dv Then MsgBox "found " & mycell.Text Sheets("Week Selection").Visible = False With Worksheets(arr) Select Case sn Case Is = "Lauren" Set r = mycell.Offset(1, 1) Case Is = "Emma" Set r = mycell.Offset(1, 2) Case Is = "Cheryl" Set r = mycell.Offset(1, 3) End Select End With End If Selection = r Selection.Interior.ColorIndex = 3 Selection.Interior.Pattern = xlSolid Selection.Interior.PatternColorIndex = xlAutomatic Next mycell Exit Sub wks.Visible = xlSheetHidden Next wks XIT: Application.EnableEvents = True Worksheets("Week Selection").Visible = True Unload Me End Sub Private Sub ComboBox2_Change() ComboBox2 = Format(ComboBox2.Value, "dd mmmm yyyy") End Sub Private Sub CommandButton1_Click() Call staffdates End Sub -- Simon Lloyd ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708 View this thread: http://www.excelforum.com/showthread...hreadid=557873 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing the format of a cell after choosing from a combobox. | Excel Discussion (Misc queries) | |||
auto change cell text colour resulting from a condition | Excel Worksheet Functions | |||
Searching array for date and changing offset colour depeding on combobox content? | Excel Programming | |||
In an excel cell,choosing a value,similar to combobox in VB - reg | New Users to Excel | |||
Change the Row Colour, using a cell condition | Excel Discussion (Misc queries) |