To format a cell pattern by VBA

[Robin Chapple]

I have a worksheet that records attendance with code that enters
"Present" when double clicked.

Can I have VBA code to format the pattern to a colour?

Thanks for your help,

Robin Chapple

 

[Anne Troy]

Why do you need VBA? Why don't you instead use Conditional Formatting?
Format-->Conditional Formatting.
****************************
Hope it helps!
Anne Troy
www.OfficeArticles.com
****************************

 

[JE McGimpsey]

One way:

Modify your existing code to add the color at the same time, for
instance:

Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Excel.Range, Cancel As Boolean)
If Not Intersect(Target.Cells, Range("A2:J100")) Is Nothing Then
With Target
.Value = "Present"
.Interior.ColorIndex = 37
End With
End If
End Sub

 

[JE McGimpsey]

Since the OP's already running an event macro to change the cell
contents, using VBA to format the cell has several advantages:

(1) Simplicity - controlling changes to the double-clicked cell in one
place rather than 2,

(2) The OP indicated that the initiating event for the color should be
the double-click, not the content (that *might* work the same, but it
wasn't stated that way), and

(3) The OP didn't indicate that the color would continue to be dependent
on the cell content, so CF might be appropriate or not - there's not
enough info to tell.

 

[Robin Chapple]

Many thanks Anne. That worked as planned.

Robin

Why do you need VBA? Why don't you instead use Conditional Formatting?
Format-->Conditional Formatting.
****************************
Hope it helps!
Anne Troy
www.OfficeArticles.com
****************************