Conditional format for cells.

[Robin Chapple]

This is an attempt to provide an attendance form for completion at a
meeting. Currently a hard copy is printed, the record is made by pen
or pencil, a mark to show who is present, and then I have to enter
the results into my spreadsheet.

Is it possible to apply conditional formatting to the cells so that a
click changes the content of the cell between two states?

One state being default "Absent", (by colour if needed), and if the
cell is clicked the member is shown as being present, (by showing a
different colour), another click returns the cell to the default
state.

 

[T. Valko]

Try this event macro:

Assume the range of cells where you want this to happen is B1:B100.

Since you want the default value to be "Absent" you'd need to already have
"Absent" entered in those cells.

When you select a cell in the range B1:B100 it will change from "Absent" to
"Present". If you select that cell again it will change back to "Absent".

Here's the macro:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo sub_exit
If Not Intersect(Target, Range("B1:B100")) Is Nothing Then
With Target
If .Value = "Absent" Then
.Value = "Present"
Else
.Value = "Absent"
End If
End With
End If
sub_exit:
Application.EnableEvents = True
End Sub

To use this macro, in the sheet where you want this to happen, right click
the sheet tab and select View Code. This will open the VBA editor. Paste the
macro in the window that opens. Then just close that window to return to the
Excel worksheet.

Adjust the range to suit.

Biff

 

[Robin Chapple]

Many thanks. That works a treat.

Is there a way to count the cells with "Present in them?

Cheers,

Robin

 

[T. Valko]

Try this:

=COUNTIF(B1:B100,"present")

Adjust the range to suit.

Biff

 

[Robin Chapple]

Again, Many thanks. I have learned a lot. I will apply the techniques
to other projects.

Robin

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

Again, Many thanks. I have learned a lot. I will apply the techniques
to other projects.

Robin