If cell equals true then other cell equals off

[timlh42]

I am trying to write some vba code to be able to do the following:

If cell p9 equals True, then B10 equals OFF.

I have tried

If Sheet2.Range("B9").Value > "" And Sheet2.Range("P9").Value = "TRUE" Then
Sheet2.Range("B10").Value = "OFF"


I have made P9 equal True and I still get no result whatsoever.

I can't seem to find what I'm missing.

Can anyone help?

 

[BrianJN1]

Sub Off()

If (Range("P1").Value = True And Range("B9").Value = "") Then
Range("B10").Value = "OFF"
End If
End Sub

Note that True is not quoted, it is a Boolean value and not a string.

 

[timlh42]

Sweet!!! Works flawlessly!!!!!!!

Thank You so much!!!!!

I do have one more question though. Since I have 117 rows to do this same comparison, is there any way to keep from having to enter this same line of code 117 times?

 

[BrianJN1]

Yes, we could do a reiteration.
I'm not sure if this will give the result that you require but it does what you have asked.
In a test I wrote True in each cell within the range P1:P10. When running the macro I had every second row appearing as "OFF".

Anyway, the code:

Sub Off_More_Rows()
Dim x As Long
For x = 1 To 117
If (Range("P" & x).Value = True And Range("B" & x + 9).Value = "") Then
Range("B" & x + 10).Value = "OFF"
End If
Next
End Sub

 

[timlh42]

That works flawlessly!!!

Thanks again!!!!!!!!!

 

[BrianJN1]

Great. I had some doubts when I saw my test. Thank you.

This could have been simpler if you hadn't want to use VBA.
I placed this formula in B10 and dragged down:
=IF(AND(P1=TRUE,B9=""),"OFF","")