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!

    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!

Register Yourself Login