Couple of thoughts.

First the cell colouring. This code will colour/remove colour on a toggle
basis when you select a cell within your specified range



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "B2:H200" '<== change to suit
Const WS_COLOUR As Long = 38 '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Interior.ColorIndex = WS_COLOUR Then
.Interior.ColorIndex = xlColorIndexNone
Else
.Interior.ColorIndex = WS_COLOUR
End If
.Offset(0, 1).Select
End With
End If

ws_exit:
Application.EnableEvents = True
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.


This macro will highlight any complete columns within that range

Public Sub HighlightFree()
Const WS_RANGE As String = "B2:H200" '<== change to suit
Const WS_COLOUR As Long = 38 '<== change to suit
Dim oColumn As Range
Dim cCells As Long
Dim cell As Range
Dim rng As Range

For Each oColumn In Range(WS_RANGE).Columns
cCells = 0
For Each cell In oColumn.Cells
If cell.Interior.ColorIndex = WS_COLOUR Then
cCells = cCells + 1
End If
Next cell
If cCells = oColumn.Cells.Count Then
If rng Is Nothing Then
Set rng = oColumn
Else
Set rng = Union(rng, oColumn)
End If
End If
Next oColumn

If Not rng Is Nothing Then rng.Select

End Sub

Put it in a standard code module.


--
---
HTH

Bob

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



wrote in message
oups.com...



I have the incredibly tedious task of trawling through peoples diaries
(Lotus Notes) for time slots when people are either free or busy (for
a potential meeting)

e.g.

Column 1 9am - 10am
Column 2 10am-11pm

(.. and so on until 4pm-5pm)


The rows are the individual peoples names


e.g.
Row 1 Adam
Row 2 Brian
Row 3 Chris

(and so on.. depending on how many people there are)


What I do is simply shade/fill cells when people are free in GREEN
...and when one person is busy I shade them GREEN

(a bit tedious itself as I have to select each cell, then choose the
fill colour)




After I have put in all the data, I scan along and look for columns
that are entirely green (as this would mean everyone is available at
that specific time). Is there anyway to get excel to automatically do
this? (when there are 30 or 40 people involved it becomes tedious
looking through - with a few people it's simple)


(by the way I cannot use Lotus Notes' built in calendar planning
feature as not ALL people's calendars are electronic. And I need to
present the grid to my boss - for example to prove there really are no
free time slots on any given morning/afternoon - that I really have
bothered to search through everyone's diaries)