Hilight The Currently Active Cell

[xcelion]

Hi All,

Iam a excel newbie.I need a help. i want to change the fill color o
the currently active cell or selection to color of my choice.Only th
selected cells or cell must have this color and all other cells mus
have the normal color.Can anybody help me on this
Thanks in advance


Thanks
Xcelio

 

[Andibevan]

Trying recording a macro that does what you want (Tools>Macros) then look at
the created code in the VB editor (Press Alt+F11).

This is quite a good way of learning the basics. The code generated when
you do this will be exactly what you want

 

[Guest]

Just a word of caution with the macro recorder - it mimics every time you
select a cell and builds code that is very long. Often you can compact the
lines and get more robust code. A small example:

Recorder:
' just copying from cell A1 on Sheet1 to cell A1 on Sheet2
Workbooks("Test1.xls").Activate
Sheets("Sheet1").Select
Range("A1").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Paste

Alternative:
Workbooks("Test1.xls").Sheets("Sheet1").Range("A1").Copy
Workbooks("Test1.xls").Sheets("Sheet2").Range("A1").Paste

This has the advantage that you don't need to have the Test1.xls book active
for the code to work.

To answer your question, the following will do the trick:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' clear previous colour
Target.Parent.Cells.Interior.ColorIndex = xlColorIndexNone
Target.Interior.ColorIndex = 3 ' <--- change this number to get a
different colour
' or use
Target.Interior.Color = RGB (255,0,0)
End Sub

Paste this code into the module for the sheet in which you want to highlight
the cells. This will only work on that sheet though.
--

Simon

Trying recording a macro that does what you want (Tools>Macros) then look at
the created code in the VB editor (Press Alt+F11).

This is quite a good way of learning the basics. The code generated when
you do this will be exactly what you want

 

[xcelion]

Thank simon.
But what about resetting color back to orginal color once the selection
is lost ?

 

[Guest]

Aha! Now that's a little bit more involved. You need to store the colours of
all the cells previously selected.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rngPrevValue As Range
Dim cell As Range

Set rngPrevValue = ThisWorkbook.Worksheets("Sheet2").Range("A1") ' <--
change this if need be
'set previous selection back to orig colour
Do Until IsEmpty(rngPrevValue)
Range(rngPrevValue.Value).Interior.ColorIndex =
rngPrevValue.Offset(ColumnOffset:=1).Value
'clear the values
rngPrevValue.Resize(ColumnSize:=2).ClearContents
' movedown to next value stored
Set rngPrevValue = rngPrevValue.Offset(RowOffset:=1)
Loop
Set rngPrevValue = ThisWorkbook.Worksheets("Sheet2").Range("A1") '<--
same as before

' store current values before changing them
For Each cell In Target
rngPrevValue.Value = cell.Address(External:=True)
rngPrevValue.Offset(ColumnOffset:=1).Value = cell.Interior.ColorIndex
Set rngPrevValue = rngPrevValue.Offset(RowOffset:=1)
Next cell
Target.Interior.ColorIndex = 3

If Not (rngPrevValue Is Nothing) Then Set rngPrevValue = Nothing

End Sub

 

[xcelion]

Thanks Simon Thanks a lot

It is really fast.But i need a little bit of explanation about your
logic since Iam a newbie