macro to work out RAG status Hi. I have a simple table which i want to automatic

Question

macro to work out RAG status

Hi. I have a simple table which i want to automatically set the RAG status. This will be worked out based on a few criteria.

To start with i will explain the table. It has 3 columns. Column A has a date, B has a number(represents hours allocated) and column C has the persons name.

The criteria for RAG status will hopefully show on every day whether a user is Green, Amber or Red. So for example on the 10th, if i have 3 records, all with my name in column C. Column b shows the numbers 1, 3 and 2 for these records. The total for the 10th is therefore 6 hours.

The RAG status is worked out as green being 1-3, amber is 3.01 to 5 and then anything over 5 is red.

Would this be possible? Sorry if i havent explained this very well..

Thanks !!

Explanation / Answer

Hi..

You can do this using conditional formatting.

Suppose your data extends no further than row 1000.

With B2:B1000 selected and B2 the active cell,

Home > Conditional Formatting > New Rule > Use a formula ... > Formula is

=SUMIF(C$2:C$1000,C2,B$2:B$1000)>5, Stop if true
> Format > Patterns > Red

Home > Conditional Formatting > New Rule > Use a formula ... > Formula is

=SUMIF(C$2:C$1000,C2,B$2:B$1000)>3, Stop if true
> Format > Patterns > Amber

Home > Conditional Formatting > New Rule > Use a formula ... > Formula is

=SUMIF(C$2:C$1000,C2,B$2:B$1000)>=1, Stop if true
> Format > Patterns > Green

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.