color cells by clicking

Hello
Thanks to all that have answered my last posts, and now for another please.

I have 3 columns B, C, D. I would like to be
able to select any blank cell in column B and turn only that cell yellow, and
of course the other columns will have there own colors. These cells will
always remain blank, and will only be those three columns. Can this be done

Thankyou Allan

Would like to see a solution to going one step further with this, where one
page links to a cell on another page. And when the other page is brought
into focus the cell that the previous page is linked to highlights with a
different color?



"A.R.J Allan Jefferys" <[email protected]> wrote in
message news:[email protected]...
> Hello
> Thanks to all that have answered my last posts, and now for another
> please.
>
> I have 3 columns B, C, D. I would like to
> be
> able to select any blank cell in column B and turn only that cell yellow,
> and
> of course the other columns will have there own colors. These cells will
> always remain blank, and will only be those three columns. Can this be
> done
>
> Thankyou Allan

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.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"A.R.J Allan Jefferys" <[email protected]> wrote in
message news:[email protected]...
> Hello
> Thanks to all that have answered my last posts, and now for another
please.
>
> I have 3 columns B, C, D. I would like to
be
> able to select any blank cell in column B and turn only that cell yellow,
and
> of course the other columns will have there own colors. These cells will
> always remain blank, and will only be those three columns. Can this be
done
>
> Thankyou Allan

Many thanks Bob

It works like a charm. I thought i Should Post it since it works .

Many thanks
Allan Jefferys

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
Sheets(ActiveSheet.Index - 1).Rows(ActiveCell.Row).Copy
ActiveSheet.Paste
end sub


"WylieDude" wrote:

> Would like to see a solution to going one step further with this, where one
> page links to a cell on another page. And when the other page is brought
> into focus the cell that the previous page is linked to highlights with a
> different color?
>
>
>
> "A.R.J Allan Jefferys" <[email protected]> wrote in
> message news:[email protected]...
> > Hello
> > Thanks to all that have answered my last posts, and now for another
> > please.
> >
> > I have 3 columns B, C, D. I would like to
> > be
> > able to select any blank cell in column B and turn only that cell yellow,
> > and
> > of course the other columns will have there own colors. These cells will
> > always remain blank, and will only be those three columns. Can this be
> > done
> >
> > Thankyou Allan
>
>
>

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" <[email protected]> wrote in message
news:%[email protected]...
> 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.
>
> --
>
> HTH
>
> Bob Phillips
>
> (remove nothere from the email address if mailing direct)
>
> "A.R.J Allan Jefferys" <[email protected]> wrote
> in
> message news:[email protected]...
>> Hello
>> Thanks to all that have answered my last posts, and now for another
> please.
>>
>> I have 3 columns B, C, D. I would like to
> be
>> able to select any blank cell in column B and turn only that cell yellow,
> and
>> of course the other columns will have there own colors. These cells will
>> always remain blank, and will only be those three columns. Can this be
> done
>>
>> Thankyou Allan
>
>

'----------------------------------------------------------------
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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
> > 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.
> >
> > --
> >
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from the email address if mailing direct)
> >
> > "A.R.J Allan Jefferys" <[email protected]>
wrote
> > in
> > message news:[email protected]...
> >> Hello
> >> Thanks to all that have answered my last posts, and now for another
> > please.
> >>
> >> I have 3 columns B, C, D. I would like
to
> > be
> >> able to select any blank cell in column B and turn only that cell
yellow,
> > and
> >> of course the other columns will have there own colors. These cells
will
> >> always remain blank, and will only be those three columns. Can this be
> > done
> >>
> >> Thankyou Allan
> >
> >
>
>

Thank you, worked like a charm...........

Cheers


"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> '----------------------------------------------------------------
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:%[email protected]...
>> > 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.
>> >
>> > --
>> >
>> > HTH
>> >
>> > Bob Phillips
>> >
>> > (remove nothere from the email address if mailing direct)
>> >
>> > "A.R.J Allan Jefferys" <[email protected]>
> wrote
>> > in
>> > message news:[email protected]...
>> >> Hello
>> >> Thanks to all that have answered my last posts, and now for another
>> > please.
>> >>
>> >> I have 3 columns B, C, D. I would like
> to
>> > be
>> >> able to select any blank cell in column B and turn only that cell
> yellow,
>> > and
>> >> of course the other columns will have there own colors. These cells
> will
>> >> always remain blank, and will only be those three columns. Can this be
>> > done
>> >>
>> >> Thankyou Allan
>> >
>> >
>>
>>
>
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > '----------------------------------------------------------------
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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" <[email protected]> wrote in message
> >> news:%[email protected]...
> >> > 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.
> >> >
> >> > --
> >> >
> >> > HTH
> >> >
> >> > Bob Phillips
> >> >
> >> > (remove nothere from the email address if mailing direct)
> >> >
> >> > "A.R.J Allan Jefferys" <[email protected]>
> > wrote
> >> > in
> >> > message news:[email protected]...
> >> >> Hello
> >> >> Thanks to all that have answered my last posts, and now for another
> >> > please.
> >> >>
> >> >> I have 3 columns B, C, D. I would like
> > to
> >> > be
> >> >> able to select any blank cell in column B and turn only that cell
> > yellow,
> >> > and
> >> >> of course the other columns will have there own colors. These cells
> > will
> >> >> always remain blank, and will only be those three columns. Can this be
> >> > done
> >> >>
> >> >> Thankyou Allan
> >> >
> >> >
> >>
> >>
> >
> >
>
>
>

Oh my

I seem to have lost my offset since i put in those three colums. The offset
was 5, so i thought it would now be 8 but no.
What did i do wrong

"WylieDude" wrote:

> Thank you, worked like a charm...........
>
> Cheers
>
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > '----------------------------------------------------------------
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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" <[email protected]> wrote in message
> >> news:%[email protected]...
> >> > 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.
> >> >
> >> > --
> >> >
> >> > HTH
> >> >
> >> > Bob Phillips
> >> >
> >> > (remove nothere from the email address if mailing direct)
> >> >
> >> > "A.R.J Allan Jefferys" <[email protected]>
> > wrote
> >> > in
> >> > message news:[email protected]...
> >> >> Hello
> >> >> Thanks to all that have answered my last posts, and now for another
> >> > please.
> >> >>
> >> >> I have 3 columns B, C, D. I would like
> > to
> >> > be
> >> >> able to select any blank cell in column B and turn only that cell
> > yellow,
> >> > and
> >> >> of course the other columns will have there own colors. These cells
> > will
> >> >> always remain blank, and will only be those three columns. Can this be
> >> > done
> >> >>
> >> >> Thankyou Allan
> >> >
> >> >
> >>
> >>
> >
> >
>
>
>

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" <[email protected]> wrote in
message news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > '----------------------------------------------------------------
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > >> 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" <[email protected]> wrote in message
> > >> news:%[email protected]...
> > >> > 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.
> > >> >
> > >> > --
> > >> >
> > >> > HTH
> > >> >
> > >> > Bob Phillips
> > >> >
> > >> > (remove nothere from the email address if mailing direct)
> > >> >
> > >> > "A.R.J Allan Jefferys" <[email protected]>
> > > wrote
> > >> > in
> > >> > message news:[email protected]...
> > >> >> Hello
> > >> >> Thanks to all that have answered my last posts, and now for
another
> > >> > please.
> > >> >>
> > >> >> I have 3 columns B, C, D. I would
like
> > > to
> > >> > be
> > >> >> able to select any blank cell in column B and turn only that cell
> > > yellow,
> > >> > and
> > >> >> of course the other columns will have there own colors. These
cells
> > > will
> > >> >> always remain blank, and will only be those three columns. Can
this be
> > >> > done
> > >> >>
> > >> >> Thankyou Allan
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >
> >

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" <[email protected]> wrote in
> message news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > '----------------------------------------------------------------
> > > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > >> 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" <[email protected]> wrote in message
> > > >> news:%[email protected]...
> > > >> > 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.
> > > >> >
> > > >> > --
> > > >> >