How2 change active cell border colour.

[James Perry]

Hello guys & here's my Q.


All of us like colours. So how can i make the borders of the "Active Cell" a different colour.

Every time i click on a new worksheet i would like the borders of the active cell have a different color.

For instance if i select worksheet1 the borders of the active cell could be Red.

If i select worksheet 2 it could be Green.

& worksheet 3 could be Blue.

And so on.


Thank u & best wishes,

James

 

[bobhc]

Good day James

Try this code

[pre]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.FormatConditions.Delete
Target.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
Target.FormatConditions(1).Interior.ColorIndex = 27
End Sub

[/pre]

 

[bobhc]

James

You could put this code into a module to see the 56 VBA colours and then choose the colour number you want.

[pre]

Sub colors56()
'57 colors, 0 to 56
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual   'pre XL97 xlManual
Dim i As Long
Dim str0 As String, str As String
For i = 0 To 56
Cells(i + 1, 1).Interior.ColorIndex = i
Cells(i + 1, 1).Value = "[Color " & i & "]"
Cells(i + 1, 2).Font.ColorIndex = i
Cells(i + 1, 2).Value = "[Color " & i & "]"
str0 = Right("000000" & Hex(Cells(i + 1, 1).Interior.Color), 6)
'Excel shows nibbles in reverse order so make it as RGB
str = Right(str0, 2) & Mid(str0, 3, 2) & Left(str0, 2)
'generating 2 columns in the HTML table
Cells(i + 1, 3) = "#" & str & "#" & str & ""
Cells(i + 1, 4).Formula = "=Hex2dec(""" & Right(str0, 2) & """)"
Cells(i + 1, 5).Formula = "=Hex2dec(""" & Mid(str0, 3, 2) & """)"
Cells(i + 1, 6).Formula = "=Hex2dec(""" & Left(str0, 2) & """)"
Cells(i + 1, 7) = "[Color " & i & ")"
Next i
done:
Application.Calculation = xlCalculationAutomatic  'pre XL97 xlAutomatic
Application.ScreenUpdating = True
End Sub

[/pre]

 

[James Perry]

Million thanks bobhc 4 the first VB solution. I am trying it out but i'm getting a "Expected End Sub" error.

The second VB gives a list of 56 colours on worksheet1 where the macro is run.

But how do i select any one colour in worksheet2 so that the borders of the active cell assume the selected colour.

Thanks again & stay blessed.

James

 

[bobhc]

Good day James


Not sure why you are getting the error as the code is tested and works, perhaps an upload so that we can see your workbook.


The second code displays the colours, you find on that you like and then put its colour number into the first code.

 

[James Perry]

Thanks again. Will try i & get back to u.