Activate Condition When Another Cell Is Selected

[T Tran]

Is it possible to have conditions on cells that activate upon another cell
being selected?

For example:
Column A contains dates;
Row 1 contains personnel names;
Data reflects personnel's attendance;

If a date is selected, is it possible to have the cells (of the personnel
who are present) highlighted and bold based on whether or not they are
present?

 

[Bob Phillips]

Assuming the grid where the people are marks as present ia named range
called data, and you mark them with an x

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cell As Range

If Target.Column = 1 Then

Me.Range("data").Cells.Interior.ColorIndex = xlColorIndexNone
For Each cell In Me.Range("data").Rows(Target.Row -
Me.Range("data").Cells(1, 1).Row + 1).Cells

If cell.Value = "x" Then

cell.Interior.ColorIndex = 38
End If
Next cell
End If
End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[paul.robinson]

Hi
Put this in the codemodule for your sheet (double click sheet name in
VB Editor). I'm assuming dates are in the left most column, names are
on the top row and the data area has a 1 in the cell if a persaon is
in on that date.
Select the whole table, including columns, and give it the name MyData
(Insert, Names, Define..click Add button)
Cell will be coloured red and 1 put in bold when a date is clicked.
Click off the dates to remove the effect.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Integer
If Not Intersect(Target, Range("MyData").Columns(1)) Is Nothing Then
If Target.Count = 1 Then
For i = 1 To Range("MyData").Columns.Count - 1
With Target.Offset(0, i)
If .Value = 1 Then
.Font.Bold = True
.Interior.ColorIndex = 3
End If
End With
Next i
End If
Else
With Range("MyData")
.Font.Bold = False
.Interior.ColorIndex = 0
End With
End If

End Sub

regards
Paul