Formula for if number is 7 or higher to add 'Fully Utilised' in a cell

[Patriot2879]

Hi good morning, please can you help I want a formula for; IF Cell O2:O17 = 7 or greater then cell M2:M17 to add note in cell 'Fully Utilised' please can you help.

 

[RoryA]

=IF(O2>=7,"Fully utliised","")

and copy down.

 

[Patriot2879]

Great thankyou, just realized though in the cell where I want the formula to go I also have a drop down list in there, is there any way to put the code in another cell but to still update M2 from O2?

 

[RoryA]

Only using code.

 

[Patriot2879]

ok thanks

 

[RoryA]

If O2:O17 are manually entered, rather than formulas, you'd just use a Worksheet_Change event to update the corresponding cells in M when the values are changed.

 

[Patriot2879]

hi O2 to O17 is a formula to count (=SUM(C4:J4) would that still be okay? how would I do the worksheet_change event?

 

[RoryA]

You'd need worksheet_calculate if they are formulas. Something like this:

private sub worksheet_calculate()
application.enableevents = false
dim rw as long
for rw = 2 to 17
if cells(rw, "O").value >= 7 and cells(rw, "M").value <> "Fully utilised" then cells(rw, "M").value = "Fully utilised"
next rw
application.enableevents = true
end sub

That needs to go into the worksheet's code module (right-click the tab, choose View Code and then paste that in).

 

[Patriot2879]

That's amazing and works fab thank you very much

 

[RoryA]

Glad to help.