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 i have added to dropbox i hope it makes sense sheet2 is where i add the data, as you can see some cells have turned green as they have a total of 6 or higher and this should autopopulate column O as fully utilised, ahwat i need it to do as well is if P=1 then O should be fully utilised.
NC Handover MASTER1211212121212121212.xlsm

 

[Joe4]

I cannot download files from my current location (I will be able to when I am at home, but that won't be for quite a few hours).
There is a tool here that allows you to post screen images. Information on that can be found here: XL2BB - Excel Range to BBCode

Really, what I am asking is quite simple. You are telling me it is not working. So I am just asking you for an example of it not working.
So, just look at your sheet, find a row where it is not working, and give me the details. Maybe something like this:
On row 14, the value in column M is 14, the value in column N is 20 and the value in column P is 2, and column O is not returning the expected string of "Fully Utilized".

That is all I am looking for you to tell me, the EXACT details of an example that is not working.

 

[Patriot2879]

Ok thank you - ok then on row 4 the value in column N is 7, the value in P50 is 1 and column O is not returning the expected string of "Fully Utilised".

 

[Joe4]

Ok thank you - ok then on row 4 the value in column N is 7, the value in P50 is 1 and column O is not returning the expected string of "Fully Utilised".

Wait, you said that on row 4, the value in column N is 7, but the value in P50 is 1.
So you are checking two different rows there, row 4 and row 50.
I thought you were wanting to check columns M,N, and P and compare for EACH row SEPARATELY.
Are you really saying that if such a value exists in ANY row in the ENTIRE range (and not to treat each row SEPARATELY)?
If so, then which row exactly in column O should be changed to "Fully Utilised", row 7 or row 50?

 

[Patriot2879]

Wait, you said that on row 4, the value in column N is 7, but the value in P50 is 1.
So you are checking two different rows there, row 4 and row 50.
I thought you were wanting to check columns M,N, and P and compare for EACH row SEPARATELY.
Are you really saying that if such a value exists in ANY row in the ENTIRE range (and not to treat each row SEPARATELY)?
If so, then which row exactly in column O should be changed to "Fully Utilised", row 7 or row 50?

 

[Patriot2879]

Hi each row that has a number of 6 or greater in N or M and in P if 1 or greater then column O needs to be fully utilised, the range is from 4 to 54. it is for the entire range

 

[Joe4]

Hi each row that has a number of 6 or greater in N or M and in P if 1 or greater then column O needs to be fully utilised, the range is from 4 to 54. it is for the entire range

Please address the issue I brought up in my previous post, because the example you posted doesn't seem to follow this rule, as you quoted two different row numbers in your example (row 7 and row 50)

 

[Patriot2879]

Please address the issue I brought up in my previous post, because the example you posted doesn't seem to follow this rule, as you quoted two different row numbers in your example (row 7 and row 50)

sorry i am confused could you have a look at the attachment when you are home this will explain it better i think

 

[Joe4]

OK, I will take a look tonight when I am home.

 

[Joe4]

I downloaded your spreadsheet, and I see two big problems.

First, you altered my code. You aren't even referencing the correct columns. Here is the code I gave you:

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, "P").Value >= 1 Then Cells(rw, "O").Value = "Fully Utilised"
Next rw
Application.EnableEvents = True
End Sub

For some reason, you changed all the column references, and here is the code you are using:

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

Why did you change the column references? My code seems to match your spreadsheet.

Secondly, on the sample spreadsheet, I see NO examples meeting your criteria.
There are no rows where the value in column M or N is 6 or greater, and the value in column P is 1 or greater.

If you replace your code with the code that I gave you, and if you have an example where the value in column M is 6, the value in column N is 6, and the value in column P is 1, it works.
Column O will display "Fully Utilized" and turn green, as it should.