color cells by clicking

  • Thread starter A.R.J Allan Jefferys
  • Start date
A

A.R.J Allan Jefferys

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
 
W

WylieDude

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?
 
B

Bob Phillips

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

A.R.J Allan Jefferys

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
 
W

WylieDude

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
 
B

Bob Phillips

'----------------------------------------------------------------
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)
 
A

A.R.J Allan Jefferys

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
 
A

A.R.J Allan Jefferys

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
 
B

Bob Phillips

You have lost me, what are you trying to do?

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
A

A.R.J Allan Jefferys

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
 
Top