Shade a cell

[Lavanya]

I need a cell to get shaded when i click on it. how can i do that?

 

[Bob Phillips]

'----------------------------------------------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'----------------------------------------------------------------
Cells.FormatConditions.Delete
With Target
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = 36
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

(replace somewhere in email address with gmail if mailing direct)

 

[Don Guillett]

Right click sheet tab>view code>insert this

'McCurdy.Here is something inspired by Don Guillett.
Private Sub Worksheet_selectionChange(ByVal Target As Range)
Dim MyRng As Range
Set MyRng = Target '.EntireRow
Application.EnableEvents = False
On Error GoTo end1
Application.Cells.FormatConditions.Delete
'Application.Cells.Font.Size = 10
With MyRng
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=ROW()=ROW(INDIRECT(CELL(""address"")))"
With .FormatConditions(1).Font
.Bold = True
.Italic = False
.ColorIndex = 1
End With
'.Font.Size = 20
.FormatConditions(1).Interior.ColorIndex = 36
End With
end1:
Application.EnableEvents = True
End Sub

 

[Don Guillett]

The one I posted will NOT

 

[Dave Peterson]

You sure?

Application.Cells.FormatConditions.Delete

Looks like it would destroy any existing conditional formats.

 

[Don Guillett]

Dave,

Test it. You can even add new.

 

[Dave Peterson]

I did before I posted.

I put 7 in A1.
I used a CF rule #1 of value = 7 and changed the fill color.
I finished up and the CF was in effect.

I added your code behind the worksheet and selected a different location. Then
back to A1 and my shading was gone. I looked at the CF rule and yours was in
its place.

What am I missing?

 

[Don Guillett]

Sorry, I missed the CF reference.

 

[Dave Peterson]

Whew! <vbg>

Sorry, I missed the CF reference.

 

[JMay]

FYI.................

Hey Guys... On this subject a few days back -- I was rummaging around
In my treasure-chest of macros and came across the following (which
allows
for the highlighting of the activecell WITHOUT affecting any previously
established Conditional Formatting.. << Pretty neat>> Your Comments?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static OldCell As Range
If Application.CutCopyMode = 0 Then
If Not OldCell Is Nothing Then
OldCell.Interior.ColorIndex = xlColorIndexNone
OldCell.Borders.LineStyle = xlLineStyleNone
End If
Set OldCell = Target
OldCell.Interior.ColorIndex = 6
OldCell.Borders.LineStyle = xlContinuous
Else
If OldCell Is Nothing Then
Set OldCell = Target
Else
Set OldCell = Union(OldCell, Target)
End If
End If
End Sub


Happy New Year !!
Thanks for all your help in 2006, 2005, 2004...!!

Sorry, I missed the CF reference.