VBA & Conditional Format

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,004
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
VBA Code:
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.
Code:
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

Active Member
Joined
Jan 27, 2010
Messages
415
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

Well-known Member
Joined
Feb 1, 2018
Messages
1,004
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?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,770
Office Version
365
Platform
Windows
If I am understanding you, I think you want something like this:
VBA Code:
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

Well-known Member
Joined
Feb 1, 2018
Messages
1,004
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,770
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Feb 1, 2018
Messages
1,004
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,770
Office Version
365
Platform
Windows
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) )
 

Forum statistics

Threads
1,085,151
Messages
5,382,002
Members
401,765
Latest member
Parrotdise

Some videos you may like

This Week's Hot Topics

Top