Clearing A Row

[alex1982]

I have a macro up and running that counts the colour of cells. I have
added a drop down menu which when one of the options is selected it
turns the whole row grey. However, my macro still counts the colours
that were orginalling in the drop down menu before anything was
selected. Is there a macro that will clear a row of cells when
something from the drop down menu is selected?

 

[Tom Ogilvy]

If your using data validation with the list option to produce the dropdown,
then you can use the worksheet change event in versions of excel after xl97.
I am not sure I understand you requirement, so you may want to elaborate with
some specifics.

If you are producing your colours with Conditional formatting, it is
unlikely a function will be able to count cells based on the visual
appearance of the color since the actual colorindex of the cell is not
changed by conditional formatting.

 

[alex1982]

Basically the work sheet I am working on is a daily schedule. column A
has peoples named listed, E is a drop down menu if the person is in
work its blank but if they are on A/L or Sick you select the field.
F-AC are 30 min time slots which are coloured to which activity the
person is doing. I have a macro up and running that will count the
coloured cells to work out how many hours work there is for each
department. If someone is sick you select them from the drop down menu
and there whole line is greyed out, this is done via conditional
formatting. However, my macro is still counting the actual colour of
the cell b4 the conditional formatting took place. Is there away around
this?