Previous Topic Next Topic

Conditional formatting

Closed
Steve - Aug 26, 2016 at 10:22 AM
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 - Aug 26, 2016 at 03:23 PM
Hello,

Can I use conditional formatting to change a number from positive to negative based on the text in another cell? For example, I have a file with column A has either Buy or Sell in each cell and column B has the amount bought or sold, which is always a positive number. I'd like a formula that would change the number in column B to a negative if the cell next to it says 'Sell' and leave alone (positive) if it says 'Buy'. Is there a way to do this?

1 response

Answer 1 / 1
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 147
Aug 26, 2016 at 03:23 PM
Steve, Good afternoon.

Try to use: 

Sub ChangeToNegative()
 For i = 1 To 1500
   If Range("A" & i).Value = "Sell" Then
      Range("B" & i).Value = Range("B" & i).Value * -1
   End If
 Next
End Sub

Adapt the value 1500 to your reality.

Is this what you want?
I hope it helps.
0

Similar discussions

How to use conditional format based on text?
Leanne99 -
Mooseman -

26 responses
Highlight cell if another cell contains specific text
Abbi -
vcoolio -

1 response
Highlight cell if another cell contains any text
LvH -
LvH -

4 responses
Conditional formating - apply on more cells
Biljana -
Raj -

32 responses
one cell will turn green when the two other cells match
diestra123 -
diestra123 -

13 responses