Sorry about that!
I have a 31 page workbook which is a bookin sheet. In column "A " there are
the room numbers which when clicked, the activecell goes back one sheet and
retrieves the data on that row and brings it forward to the activesheet. now
once this is done a cell in column 12 is selected as the offset to the row
in use which is a counter on how many stays so far eg: "1" is copied from
sheet1 and turned into "2" and so on for 31 sheets if the same person books
the same bed each day. You helped out the other day with a color scheme for
three colums b, c, d which worked very well, but because i inserted the
three column after the sheet was designed, The cell that was automatically
selectedas the counter is not in focus anymore and i am unable to get it
back. i have changed the offset number, but no go.
I can only hope this is what you wanted to Know, or i have confused you
more, and will not waste anymore of your time.
Thanks again Allan
"Bob Phillips" wrote:
> You have lost me, what are you trying to do?
>
> --
>
> HTH
>
> Bob Phillips
>
> (remove nothere from the email address if mailing direct)
>
> "A.R.J Allan Jefferys" <(E-Mail Removed)> wrote in
> message news:6D3D95AB-6AA3-438A-AECC-(E-Mail Removed)...
> > Thanks for that bit of code Bob, but i am not sure if it should go at the
> end
> > or the middle of the module, so what i did was this:
> >
> > Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
> > Boolean)
> > Application.CommandBars("cell").Enabled = False
> > Selection.Interior.ColorIndex = xlNone
> > Selection.ClearContents
> > End Sub
> > Yours would be better because it is automatic.
> >
> > I know it looks long because it is, but i was not sure in one bit of code,
> > on how to keep the counter going
> >
> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > On Error GoTo ws_exit
> > Application.EnableEvents = False
> > With Target
> > If .Column = 2 Then
> > .Interior.ColorIndex = 6 'yellow
> > ElseIf .Column = 3 Then
> > .Interior.ColorIndex = 5 'blue
> > ElseIf .Column = 4 Then
> > .Interior.ColorIndex = 3 'red
> > End If
> > End With
> >
> > ws_exit:
> > Application.EnableEvents = True
> > On Error GoTo 0
> > If ActiveSheet.Index = 1 Then Exit Sub
> > If ActiveCell.Row < 41 Then Exit Sub
> > If ActiveCell.Row > 94 Then Exit Sub
> > If ActiveCell.Column > 1 Then Exit Sub
> > Sheets(ActiveSheet.Index - 1).Rows(ActiveCell.Row).Copy
> > ActiveSheet.Paste
> >
> > ActiveCell.Offset(columnoffset:=5).Select
> > If ActiveCell.FormulaR1C1 = "1" Then
> > Selection.ClearContents
> > ActiveCell.FormulaR1C1 = "2"
> > End
> > End If
> > If ActiveCell.FormulaR1C1 = "2" Then
> > Selection.ClearContents
> > ActiveCell.FormulaR1C1 = "3"
> > End
> > End If
> > If ActiveCell.FormulaR1C1 = "3" Then
> > Selection.ClearContents
> > ActiveCell.FormulaR1C1 = "4"
> > End
> > End If
> > If ActiveCell.FormulaR1C1 = "4" Then
> > Selection.ClearContents
> > ActiveCell.FormulaR1C1 = "5"
> > End
> > End If
> > If ActiveCell.FormulaR1C1 = "5" Then
> > Selection.ClearContents
> > ActiveCell.FormulaR1C1 = "6"
> > End
> > End If
> > If ActiveCell.FormulaR1C1 = "6" Then
> > Selection.ClearContents
> > ActiveCell.FormulaR1C1 = "7"
> > End
> > End If
> > If ActiveCell.FormulaR1C1 = "7" Then
> > Selection.ClearContents
> > ActiveCell.FormulaR1C1 = "8"
> > End
> > End If
> > If ActiveCell.FormulaR1C1 = "8" Then
> > Selection.ClearContents
> > ActiveCell.FormulaR1C1 = "9"
> > End
> > End If
> > If ActiveCell.FormulaR1C1 = "9" Then
> > Selection.ClearContents
> > ActiveCell.FormulaR1C1 = "10"
> > End
> > End If
> > If ActiveCell.FormulaR1C1 = "10" Then
> > Selection.ClearContents
> > ActiveCell.FormulaR1C1 = "11"
> > End
> > End If
> > If ActiveCell.FormulaR1C1 = "11" Then
> > Selection.ClearContents
> > ActiveCell.FormulaR1C1 = "12"
> > End
> > End If
> > If ActiveCell.FormulaR1C1 = "12" Then
> > Selection.ClearContents
> > ActiveCell.FormulaR1C1 = "13"
> > End
> > End If
> > If ActiveCell.FormulaR1C1 = "13" Then
> > Selection.ClearContents
> > ActiveCell.FormulaR1C1 = "14"
> > End
> > End If
> > If ActiveCell.FormulaR1C1 = "14" Then
> > Selection.ClearContents
> > ActiveCell.FormulaR1C1 = "15"
> > End
> > End If
> > If ActiveCell.FormulaR1C1 = "15" Then
> > Selection.ClearContents
> > ActiveCell.FormulaR1C1 = "16"
> > End
> > End If
> > If ActiveCell.FormulaR1C1 = "16" Then
> > Selection.ClearContents
> > ActiveCell.FormulaR1C1 = "17"
> > End
> > End If
> > If ActiveCell.FormulaR1C1 = "17" Then
> > Selection.ClearContents
> > ActiveCell.FormulaR1C1 = "18"
> > End
> > End If
> > If ActiveCell.FormulaR1C1 = "18" Then
> > Selection.ClearContents
> > ActiveCell.FormulaR1C1 = "19"
> > End
> > End If
> > If ActiveCell.FormulaR1C1 = "19" Then
> > Selection.ClearContents
> > ActiveCell.FormulaR1C1 = "20"
> > End
> > End If
> > If ActiveCell.FormulaR1C1 = "20" Then
> > Selection.ClearContents
> > ActiveCell.FormulaR1C1 = "21"
> > End
> > End If
> > If ActiveCell.FormulaR1C1 = "21" Then
> > Selection.ClearContents
> > ActiveCell.FormulaR1C1 = "22"
> > End
> > End If
> > If ActiveCell.FormulaR1C1 = "22" Then
> > Selection.ClearContents
> > ActiveCell.FormulaR1C1 = "23"
> > End
> > End If
> > If ActiveCell.FormulaR1C1 = "23" Then
> > Selection.ClearContents
> > ActiveCell.FormulaR1C1 = "24"
> > End
> > End If
> > If ActiveCell.FormulaR1C1 = "24" Then
> > Selection.ClearContents
> > ActiveCell.FormulaR1C1 = "25"
> > End
> > End If
> > If ActiveCell.FormulaR1C1 = "25" Then
> > Selection.ClearContents
> > ActiveCell.FormulaR1C1 = "26"
> > End
> > End If
> > If ActiveCell.FormulaR1C1 = "26" Then
> > Selection.ClearContents
> > ActiveCell.FormulaR1C1 = "27"
> > End
> > End If
> > If ActiveCell.FormulaR1C1 = "27" Then
> > Selection.ClearContents
> > ActiveCell.FormulaR1C1 = "28"
> > End
> > End If
> > If ActiveCell.FormulaR1C1 = "28" Then
> > Selection.ClearContents
> > ActiveCell.FormulaR1C1 = "29"
> > End
> > End If
> > If ActiveCell.FormulaR1C1 = "29" Then
> > Selection.ClearContents
> > ActiveCell.FormulaR1C1 = "30"
> > End
> > End If
> > If ActiveCell.FormulaR1C1 = "30" Then
> > Selection.ClearContents
> > ActiveCell.FormulaR1C1 = "31"
> > End
> > End If
> > If ActiveCell.FormulaR1C1 >= "31" Then
> > Selection.ClearContents
> > ActiveCell.FormulaR1C1 = "There are only 31 days Wally"
> > End
> > End If
> > End Sub
> >
> > does this look alright to you?
> >
> > Allan
> >
> > "WylieDude" wrote:
> >
> > > Thank you, worked like a charm...........
> > >
> > > Cheers
> > >
> > >
> > > "Bob Phillips" <(E-Mail Removed)> wrote in message
> > > news:(E-Mail Removed)...
> > > > '----------------------------------------------------------------
> > > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > > > '----------------------------------------------------------------
> > > > Dim ci As Long
> > > > Cells.FormatConditions.Delete
> > > > With Target
> > > > If .Column = 2 Then
> > > > ci = 6 'yellow
> > > > ElseIf .Column = 3 Then
> > > > ci = 5 'blue
> > > > ElseIf .Column = 4 Then
> > > > ci = 3 'red
> > > > Else
> > > > Exit Sub
> > > > End If
> > > > .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
> > > > .FormatConditions(1).Interior.ColorIndex = ci
> > > > End With
> > > >
> > > > End Sub
> > > >
> > > > 'This is worksheet event code, which means that it needs to be
> > > > 'placed in the appropriate worksheet code module, not a standard
> > > > 'code module. To do this, right-click on the sheet tab, select
> > > > 'the View Code option from the menu, and paste the code in.
> > > >
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > > "WylieDude" <(E-Mail Removed)> wrote in message
> > > > news:(E-Mail Removed)...
> > > >> Code worked great thanks. One thing that should happen is when the
> page
> > > >> is
> > > >> not in focus the color should revert to its original default color.
> Or if
> > > >> another cell on that page is selected with the mouse the highlighted
> cell
> > > >> should revert to default.
> > > >>
> > > >> Thanks alot this is very helpfull
> > > >>
> > > >>
> > > >> "Bob Phillips" <(E-Mail Removed)> wrote in message
> > > >> news:%(E-Mail Removed)...
> > > >> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > > >> >
> > > >> > On Error GoTo ws_exit
> > > >> > Application.EnableEvents = False
> > > >> > With Target
> > > >> > If .Column = 2 Then
> > > >> > .Interior.ColorIndex = 6 'yellow
> > > >> > ElseIf .Column = 3 Then
> > > >> > .Interior.ColorIndex = 5 'blue
> > > >> > ElseIf .Column = 4 Then
> > > >> > .Interior.ColorIndex = 3 'red
> > > >> > End If
> > > >> > End With
> > > >> >
> > > >> > ws_exit:
> > > >> > Application.EnableEvents = True
> > > >> > On Error GoTo 0
> > > >> >
> > > >> > End Sub
> > > >> >
> > > >> >
> > > >> > This is worksheet event code, which means that it needs to be
> > > >> > placed in the appropriate worksheet code module, not a standard
> > > >> > code module. To do this, right-click on the sheet tab, select
> > > >> > the View Code option from the menu, and paste the code in.
> > > >> >
> > > >> > --
> > > >> >
|
Similar Threads
