# A formula that change cell color depending on the priority

• Hi

My colleagues are working on multiple projects at once. The projects got different priority, so I'm looking for a formula that change the cell color if a person is working on it, depending on the project's priority.

For example if John is working on project: East(pri.1), South(pri.2) and West(pri.3). On East he got 5 remaining hours (cell E8). Then i would like that cell to turn red. For South green and West red.

I got a dummie for you to easier understand my problem. If it is still not understandable, please tell me.

I'm hoping somebody got a formula for this.

Thanks in advance!

## Files

• Re: A formula that change cell color depending on the priority

Hey,

Try this. Hope I understood you well.

Sub highlight()
For Each cell In Range("E8:l8")
If cell <> "" Then
cell.Interior.Color = vbRed
End If
Next
For Each cell In Range("E9:L9")
If cell <> "" Then
cell.Interior.Color = vbYellow
End If
Next
For Each cell In Range("E10:L10")
If cell <> "" Then
cell.Interior.Color = vbGreen
End If
Next
End Sub

• Re: A formula that change cell color depending on the priority

Thank you!
It worked perfect for the 3 projects.
But if i got 20 projects. Is there anything i can do for the macro to auto-color out from the priority without having to copy the formula for every project?

Thanks in advance!

• Re: A formula that change cell color depending on the priority

You don't need the macro in every project; as long as the project it's in is open, it'll be available to work on whatever workbook is active.

• Re: A formula that change cell color depending on the priority

No sure what you mean by without copying the formula. But what you can do is this (the easiest way I think).
go to the macro code and copy the below into another line in the macro and amend the highlighted depending of the position of the cells (for the new projects) and of the colour you chose.

For Each cell In Range("E10:L10")
If cell <> "" Then
cell.Interior.Color = vbGreen
End If
Next

what you will need to amend is in bold.

Hope this helps.

• Re: A formula that change cell color depending on the priority

Yes, copying the macro code, was what i ment by "copying the formula".
Was just afraid that the macro would be very long if I have 20 projects with that formula.
But I'm very unexperienced in VBA, so if there is not any easy way to manage this, I will just copy the code.

Thank you very much for all your help!

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!