VBA Less than formula

L

Legacy 452653

Guest
I have a warehouse mod to be completed and ive created automatic or Userform activate that when a order line is in the negative it needs to be highlighted in Reg

The formula I got is:
Private Sub Reg6_Change()
If Sheet12.Range("H6").Value < Sheet12.Range("K6").Value = "0" Then
Me.Reg6.BackColor = vbRed
Else
Me.Reg6.BackColor = vbYellow
End If
End Sub

So Cell H6 is the cell QTY ordered = 3 but in in cell K6 (Instock) = -1 because the TAB reference SUMIF indicates there is 2 in stock.

In the VBA I wanted the code to reflect Reg6 (TextBox) if cell K6 is less than ZERO please conditional format highlight in RED

It doesnt seem to work at all in either greater than so if anybody can help me out that would be great

Steve
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
843
Office Version
  1. 365
Platform
  1. Windows
The syntax on your If line doesn't make sense for what you are doing. Where does the zero come into play?

"when a order line is in the negative" What data here represents the "order line"?

This line will highlight red if H6 is less than K6:

VBA Code:
If Sheet12.Range("H6").Value < Sheet12.Range("K6").Value Then

Other than that I can't quite tell what you want to do.
 
L

Legacy 452653

Guest
Oh yeah your right!
I thought I needed to tell VBA anything below ZERO please list this as red

So thank for that ….I feel such a dork!

Steve
 

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
843
Office Version
  1. 365
Platform
  1. Windows
I am still not clear on the logic you want to use. Your original question said "order line is in the negative" and "if cell K6 is less than ZERO" but you are testing for H6 < K6. If you need to test for K6 < 0 then

VBA Code:
If Sheet12.Range("K6").Value < 0 Then

You didn't really give the whole picture here but it sounds like you have an order quantity, and an in-stock quantity. When the order quantity is entered, the in-stock quantity is updated by a formula on your worksheet. Therefore, if the in-stock quantity is negative, it means the order quantity exceeds current stock, and you want to highlight the textbox in red.

If that description isn't right then you probably need different code.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,691
Messages
5,637,845
Members
416,985
Latest member
mrindira

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top