Hilight The Currently Active Cell

[xcelion]

Hi All,

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


Thanks
Xcelion

[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

"xcelion" <[email protected]> wrote in
message news:[email protected]...
>
> Hi All,
>
> Iam a excel newbie.I need a help. i want to change the fill color of
> the currently active cell or selection to color of my choice.Only the
> selected cells or cell must have this color and all other cells must
> have the normal color.Can anybody help me on this
> Thanks in advance
>
>
> Thanks
> Xcelion
>
>
> --
> xcelion
> ------------------------------------------------------------------------
> xcelion's Profile:
http://www.excelforum.com/member.php...o&userid=16287
> View this thread: http://www.excelforum.com/showthread...hreadid=396407
>

[Simon Letten]

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


"Andibevan" wrote:

> 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" <[email protected]> wrote in
> message news:[email protected]...
> >
> > Hi All,
> >
> > Iam a excel newbie.I need a help. i want to change the fill color of
> > the currently active cell or selection to color of my choice.Only the
> > selected cells or cell must have this color and all other cells must
> > have the normal color.Can anybody help me on this
> > Thanks in advance
> >
> >
> > Thanks
> > Xcelion
> >
> >
> > --
> > xcelion
> > ------------------------------------------------------------------------
> > xcelion's Profile:
> http://www.excelforum.com/member.php...o&userid=16287
> > View this thread: http://www.excelforum.com/showthread...hreadid=396407
> >
>
>
>

[xcelion]

Thanks simon.
Yes simon it's working .But it's slow,When we are using arrow keys to move between the cells and we can see the hour glass .IS there any altrenate approach

Thanks
Xcelion

[Simon Letten]

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

--


Simon


"xcelion" wrote:

>
> Thank simon.
> But what about resetting color back to orginal color once the selection
> is lost ?
>
>
> --
> xcelion
> ------------------------------------------------------------------------
> xcelion's Profile: http://www.excelforum.com/member.php...o&userid=16287
> View this thread: http://www.excelforum.com/showthread...hreadid=396407
>
>

[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