VBA & Conditional Format

[Patriot2879]

Hi good afternoon, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to be filled with the colour Green, can you help me please? I have done Column N and M already with a mix of vba and conditional formatting, but I cant seem to add a new column (P) please can you help

Private Sub worksheet_calculate()
Application.EnableEvents = False
Dim rw As Long
For rw = 4 To 54
If (Cells(rw, "M").Value >= 6 Or Cells(rw, "N").Value >= 6) And Cells(rw, "O").Value <> "Fully Utilised" Then Cells(rw, "O").Value = "Fully Utilised"
Next rw
Application.EnableEvents = True
End Sub

I have tried changing it to the below but it doesn’t work please advice.

Private Sub worksheet_calculate()
Application.EnableEvents = False
Dim rw As Long
For rw = 4 To 54
If (Cells(rw, "M").Value >= 6 Or Cells(rw, "N").Value >= 6) And Cells(rw, "O").Value <> "Fully Utilised" Then Cells(rw, "O").Value = "Fully Utilised"
If (Cells(rw, "P").Value >= 1 And Cells(rw, "O").Value <> "Fully Utilised" Then Cells(rw, "O").Value = "Fully Utilised"
Next rw
Application.EnableEvents = True
End Sub

 

[Patriot2879]

Hi yeah sorry dont know why i changed the references, i have used your original code now, but fully utilised still doesnt come up in column O, when for example N4=7, or N5=5, or P22=1, so for excery cell in M and N if the number is 6 or more then fully utilised needs to appear in column O and turn green the same as column P if the number in one of the cells is 1 or more then fully utilised needs to appear in Column O.

 

[Joe4]

have used your original code now, but fully utilised still doesnt come up in column O, when for example N4=7, or N5=5, or P22=1, so for excery cell in M and N if the number is 6 or more then fully utilised needs to appear in column O and turn green the same as column P if the number in one of the cells is 1 or more then fully utilised needs to appear in Column O.

I am really trying to help you here, but it is really frustrating because it feels like you are really not listening to what I am saying, and I find myself repeating what I have already said over and over again. Please read what I have written very carefully and completely. Don't skip over anything. And be sure to answer all the questions I ask you.

Once again, the example you provided references different rows (N4=7, or N5=5, or P22=1) In order for this to work, all the conditions must be met on the same row.
When I tested out the code that I gave you on the spreadsheet you uploaded when those conditions were met on the same row, it did exactly what you wanted, it put "Fully Utilised" in column O and highlighted it green.

 

[Patriot2879]

Hi it only needs to say fully utilised if one of the cells =6 or =1 for example if name1 had n4=0, m4=6 and p4=0 then o4 should say fully utilised. If name1 had n5=6, m5=0 and p=0 then o5 should be fully utilised. If name3 had n6=0, m6=0 and p6=1 then o5 should be fully utilised. Hope this makes sense

 

[Patriot2879]

Hi it only needs to say fully utilised if one of the cells =6 or =1 for example if name1 had n4=0, m4=6 and p4=0 then o4 should say fully utilised. If name1 had n5=6, m5=0 and p=0 then o5 should be fully utilised. If name3 had n6=0, m6=0 and p6=1 then o5 should be fully utilised. Hope this makes sense

Or the number can be higher than 6 as well

 

[Joe4]

Hi it only needs to say fully utilised if one of the cells =6 or =1 for example if name1 had n4=0, m4=6 and p4=0 then o4 should say fully utilised. If name1 had n5=6, m5=0 and p=0 then o5 should be fully utilised. If name3 had n6=0, m6=0 and p6=1 then o5 should be fully utilised. Hope this makes sense

It sounds like you do not want AND at all, rather you want OR across the board, i.e.

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Dim rw As Long
For rw = 4 To 54
    If Cells(rw, "M").Value >= 6 Or Cells(rw, "N").Value >= 6 Or Cells(rw, "P").Value >= 1 Then Cells(rw, "O").Value = "Fully Utilised"
Next rw
Application.EnableEvents = True
End Sub

 

[Patriot2879]

that has worked thank you

 

[Joe4]

You are welcome.

I think the struggle was just figuring out what you were looking for. The original had the AND in it, and we were working off of that, but it appears maybe we should not have been, because in hindsight, that really did not give you what you wanted.

Sometimes, it is better to take a step back and start over from square one.

 

[Patriot2879]

Hiya spot on thank you i shall remember that in the future, sometimes i struggle explaining it to somebody exactly how it should be and miss some things out, i will definately take a step back i think next time and go into better detail.. thank you so much for your support

 

[Patriot2879]

Hi Joe4 sorry one more query please if ok? is there any way if the value in n or m is met to say fully utilised and if the value in p is met to say reduced hours, i have tried amending to the below but the line went red.

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Dim rw As Long
For rw = 4 To 54
    If (Cells(rw, "M").Value >= 6 Or Cells(rw, "N").Value >= 6) Then Cells(rw, "O").Value = "Fully Utilised" Or Cells(rw, "P").Value >= 1 Then Cells(rw, "O").Value = "Reduced hours"
Next rw
Application.EnableEvents = True
End Sub

 

[Joe4]

You would need to do it in two separate IF statements, probably nested, i.e.

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Dim rw As Long
For rw = 4 To 54
    If (Cells(rw, "M").Value >= 6 Or Cells(rw, "N").Value >= 6) Then 
        Cells(rw, "O").Value = "Fully Utilised" 
    Else
        If Cells(rw, "P").Value >= 1 Then 
            Cells(rw, "O").Value = "Reduced hours"
        End If
    End If
Next rw
Application.EnableEvents = True
End Sub

Just note that if ALL conditions are met, "Fully Utilised" wins, and it will return that.
If you want it the other way, then you would just flip the order of the IF...THEN statements in the code.