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

 

[6StringJazzer]

Please describe more fully "doesn't work." What are the values in M, N, O, P when it doesn't work, what is the result you expect in O, and what result do you actually get in O?

Other than when this Sub assigns "Fully Utilised" to the cell in column O, how else do column O cells get assigned values? Is there a formula there to start with?

 

[Patriot2879]

Hi thanks for your reply the values of M are M4:M54, N4:N54, O4,O54, P4:54.
If M, N value is equal to 6 or higher then O is updated with "Fully Utilsed"
What i want to add to the code is if value of P is equal to 1 then O is updated with "Fully Utilsed"
Currently the coding works for N and M to update O with fully utilised, but cant seem to add the value of P.

I have also done conditional format to coincide with the VBA Code which is:
Format only cells that contain
Cell Value
Equal to
="Fully Utilised"
Range =$O$4:$O$54
And i have formatted the colour to show as GREEN.

Hope this all makes sense and that you can help me please?

 

[Patriot2879]

Any ideas at all please? hope you can help

 

[Joe4]

If I am understanding you, I think you want something like this:

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

Also note that in your title, "Worksheet_Calculate()" should have the "W" and "C" capitalized, automatically.
If it is not, then you have probably placed the code in the wrong module.
This code needs to be placed in the sheet module that you want this to run against.

By the way, how exactly is the data in columns M, N, O, and P being updated?

 

[Patriot2879]

Hi thank you for the new code, i have tried this but now column O doesn't update with Fully Utilised. when the numbers in M, N are equel to 6 or if P is equeal to 1
I use a conditional format to coincide with the VBA Code which is:
Format only cells that contain
Cell Value
Equal to
="Fully Utilised"
Range =$O$4:$O$54
And i have formatted the colour to show as GREEN.

 

[Joe4]

Please provide an example of a row that should meet the criteria. Please tell us the following:
- the row number
- the values in columns M, N, and P in that row
- confirm that all the values in M, N, and P in that row are actually Numeric entries and not Text entries (do you know how to do that?)

Also, please answer the following questions:
- what module have you placed this code in
- how these values are being updated
(note that this code ONLY runs when data is updated, it will NOT run against existing data if no data changes are made, you need to change something for it to run!!!)

 

[Patriot2879]

Hi the row numbers are M4:M54, N4:N54 and P4:P45, the data itself is being updated in sheet2, then this autopopulates the figures into Columns C to L and then in M, N and O there is a count which populates and if the fig for example counts 6 then in O it autopopulates "Fully Utilised" and should turn Green.

I have put the VBA code into the sheet by right clicking then view code and paste in there.

 

[Joe4]

I am looking for a specific example that you say is not working, not generalities.
I want you to give me a specific row number that you are saying should be working, that is not working.
What specific row number?
What are the values in M, N, and P of that specific row?
Can you confirm that those entries are valid numbers and not numbers entered as text?
(can be confirmed with simple ISNUMBER formulas, i.e. =ISNUMBER(P6) )

 

[Patriot2879]

can i attach you an example, i am abit lost with all this sorry