PC Review
Home Forums Reviews Articles Register
Member Login:

PC Review > Forums > Newsgroups > Microsoft Excel > Microsoft Excel Discussion > color cells by clicking

Reply
Page 1 of 2 1 2
Thread Tools Rate Thread

color cells by clicking

 
 
=?Utf-8?B?QS5SLkogQWxsYW4gSmVmZmVyeXM=?=
Guest
Posts: n/a
 
      13th May 2006
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
 
Reply With Quote
 
 
 
 
WylieDude
Guest
Posts: n/a
 
      13th May 2006
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" <(E-Mail Removed)> wrote in
message news:B76C0C2D-5CFE-44CA-8362-(E-Mail Removed)...
> 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
 
Reply With Quote
 
Bob Phillips
Guest
Posts: n/a
 
      13th May 2006
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" <(E-Mail Removed)> wrote in
message news:B76C0C2D-5CFE-44CA-8362-(E-Mail Removed)...
> 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
 
Reply With Quote
 
=?Utf-8?B?QS5SLkogQWxsYW4gSmVmZmVyeXM=?=
Guest
Posts: n/a
 
      13th May 2006
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" <(E-Mail Removed)> wrote in
> message news:B76C0C2D-5CFE-44CA-8362-(E-Mail Removed)...
> > 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
>
>
>
 
Reply With Quote
 
WylieDude
Guest
Posts: n/a
 
      13th May 2006
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.
>
> --
>
> HTH
>
> Bob Phillips
>
> (remove nothere from the email address if mailing direct)
>
> "A.R.J Allan Jefferys" <(E-Mail Removed)> wrote
> in
> message news:B76C0C2D-5CFE-44CA-8362-(E-Mail Removed)...
>> 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
>
>
 
Reply With Quote
 
Bob Phillips
Guest
Posts: n/a
 
      13th May 2006
'----------------------------------------------------------------
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.
> >
> > --
> >
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from the email address if mailing direct)
> >
> > "A.R.J Allan Jefferys" <(E-Mail Removed)>
wrote
> > in
> > message news:B76C0C2D-5CFE-44CA-8362-(E-Mail Removed)...
> >> 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
> >
> >
>
>
 
Reply With Quote
 
WylieDude
Guest
Posts: n/a
 
      13th May 2006
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.
>> >
>> > --
>> >
>> > HTH
>> >
>> > Bob Phillips
>> >
>> > (remove nothere from the email address if mailing direct)
>> >
>> > "A.R.J Allan Jefferys" <(E-Mail Removed)>
> wrote
>> > in
>> > message news:B76C0C2D-5CFE-44CA-8362-(E-Mail Removed)...
>> >> 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
>> >
>> >
>>
>>
>
>
 
Reply With Quote
 
=?Utf-8?B?QS5SLkogQWxsYW4gSmVmZmVyeXM=?=
Guest
Posts: n/a
 
      14th May 2006
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.
> >> >
> >> > --
> >> >
> >> > HTH
> >> >
> >> > Bob Phillips
> >> >
> >> > (remove nothere from the email address if mailing direct)
> >> >
> >> > "A.R.J Allan Jefferys" <(E-Mail Removed)>
> > wrote
> >> > in
> >> > message news:B76C0C2D-5CFE-44CA-8362-(E-Mail Removed)...
> >> >> 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
> >> >
> >> >
> >>
> >>
> >
> >
>
>
>
 
Reply With Quote
 
=?Utf-8?B?QS5SLkogQWxsYW4gSmVmZmVyeXM=?=
Guest
Posts: n/a
 
      14th May 2006
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" <(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.
> >> >
> >> > --
> >> >
> >> > HTH
> >> >
> >> > Bob Phillips
> >> >
> >> > (remove nothere from the email address if mailing direct)
> >> >
> >> > "A.R.J Allan Jefferys" <(E-Mail Removed)>
> > wrote
> >> > in
> >> > message news:B76C0C2D-5CFE-44CA-8362-(E-Mail Removed)...
> >> >> 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
> >> >
> >> >
> >>
> >>
> >
> >
>
>
>
 
Reply With Quote
 
Bob Phillips
Guest
Posts: n/a
 
      14th May 2006
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.
> > >> >
> > >> > --
> > >> >
> > >> > HTH
> > >> >
> > >> > Bob Phillips
> > >> >
> > >> > (remove nothere from the email address if mailing direct)
> > >> >
> > >> > "A.R.J Allan Jefferys" <(E-Mail Removed)>
> > > wrote
> > >> > in
> > >> > message news:B76C0C2D-5CFE-44CA-8362-(E-Mail Removed)...
> > >> >> 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
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >
> >
 
Reply With Quote
 
 
 
Reply
Page 1 of 2 1 2

« How to bypass cell protect message/cursor focus set | using AND in a countif formula »
Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Re: How to color automatically color code sums in cells JE McGimpsey Microsoft Excel Charting 0 29th Nov 2006 04:16 AM
How do I apply color in cells ..color now shown only in print prev =?Utf-8?B?Ym9uYWRpbWk=?= Microsoft Excel Worksheet Functions 1 25th Apr 2006 08:34 PM
change fill color of a range of cells based on color of a cell? =?Utf-8?B?RGFyTWVsTmVs?= Microsoft Excel Programming 0 2nd Mar 2006 07:35 PM
Excel 2003 will not display color fonts or color fill cells =?Utf-8?B?RGF2ZUM=?= Microsoft Excel Worksheet Functions 1 11th Apr 2005 05:38 PM
My excel 2003 wont let me fill cells with color or color the tabs. =?Utf-8?B?dHJpem9n?= Microsoft Excel New Users 2 22nd Feb 2005 07:43 PM


Features
Forums Startup Database Reviews Articles FAQ / Guidelines About Us
 

Advertising
 

Newsgroups
Windows Vista Windows XP Microsoft Word Microsoft DotNet Windows 2000 AntiSpyware Microsoft Access Microsoft Excel Microsoft Outlook Hardware
 


All times are GMT +1. The time now is 08:59 PM.
Powered by vBulletin®. Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2010, Crawlability, Inc.
Contact Us - Archive - Privacy Statement - Top