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

--
xcelio
-----------------------------------------------------------------------
xcelion's Profile: http://www.excelforum.com/member.php...fo&userid=1628
View this thread: http://www.excelforum.com/showthread.php?threadid=39640

Trying recording a macro that does what you want (ToolsMacros) 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" wrote in
message ...

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

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 (ToolsMacros) 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" wrote in
message ...

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

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

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

Thanks Simon Thanks a lot

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


--
xcelion
------------------------------------------------------------------------
xcelion's Profile: http://www.excelforum.com/member.php...o&userid=16287
View this thread: http://www.excelforum.com/showthread...hreadid=396407