Using Excel/programming in Excel to plan meetings

[barbican.angel]

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)

 

[Bob Phillips]

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)

 

[RagDyeR]

If I understand you, you're clicking in every cell and coloring it,
according to whether or not a particular person has an appointment scheduled
for that cell's designated time slot.

What you might try is to use CF (conditional formatting), and start off with
the *entire* grid colored green, and then enter a character ("X" or perhaps
the appointment start time ... 9, 10, 11, etc.) into cells that are *not*
vacant, thus not having to type something into every cell.
The CF would automatically change the "occupied" cell to RED, or any other
color of your choice, to designate a time slot was not available for that
person.

Depending on which condition was more prevelant, ("open" or "taken"), you
could choose to type in the cell denoting the least prevelant condition, to
lessen the number of cells you would need to fill.

With headers in Row1 and Column A, and time slots in say B2 to L30:

Select B2 to L30 and color them green.

Select B2 to L30 again, and with the focus on B2, go to:
<Format> <Conditional Format>,
and change "Cell Value Is" to "Formula Is", and enter this formula:

=B2<>""

Then click on "Format", and choose the color you want for an occupied cell,
Then <OK> <OK>.

Now, *any* character entered into a cell in the grid will change that cell
from green to your designated color.

If, on the other hand, you have much less "vacant" cells, where you might
want to only have to type in those lesser amount of cells, *Don't* pre-color
the grid, but use the *SAME* formula:
Select B2 to L30 again, and with the focus on B2, go to:
<Format> <Conditional Format>,
and change "Cell Value Is" to "Formula Is", and enter this formula:

=B2<>""

Then click on "Format", and choose the color you want for a Vacant cell say
green,
Then <OK> <OK>.

Now, you can type an "A" for available, and that cell will turn green to
show a vacant time slot.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================




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)

 

[Harlan Grove]

(e-mail address removed) wrote...
....

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)

Right. Tedious. So don't use this approach.

If you have time slots in B1:U1 (every 1/2 hour from 8:00 AM to 6:00
PM) and names in A2:A49 (48 people), then select B2:U49, press [Ctrl]
+1, select the Number tab in the Format Cells dialog, choose the
Custom category, enter ;;; in the box just below Type: on the right,
and Click the OK button.

Next, with the same cells selected, run the menu command Format >
Conditional Formatting, leave Cell Value Is in the rightmost entry
box, change the middle entry box to 'not equal to' using its drop-down
list, enter ="" (an equal sign followed by 2 double quotes, NOTHING
ELSE) in the right entry box, click on the format button use the
abbreviated Format Cells dialog to set the background color to green,
click OK in the Format Cells dialog, then when you return to the
Conditional Formatting dialog click OK there as well.

Now all you need to do is enter anything in any of the cells in B2:U49
to indicate that a particular person is available at a particular time
- much easier than formatting each cell.

You could also use Conditional Formatting in the time heading cells in
B1:U1 to check whether all or all but one persons are available, e.g.,
for 9:00 AM to 9:30 AM (col D), cell D1's conditional format would
have Formula Is in the leftmost entry box and the formula

=COUNTBLANK(B$2:B$49)<2

in the rightmost (only other) entry box. The only other thing needed
would be to give it a distinctive format.