colored cell with negative value

[Sandie1712]

Hi

First of all i am very very new to VBA and learnt some things from youtube and tried to develop a code out of that in which i expect my cells with yellow color and negative value to be multiplied by -1 after running the program but below cited isn't working could you pl look into it and provide the right solution.

Sub Colormultiplewithnegative()Dim negvalue As Integernegvalue = -1Dim last_value As Interiorlast_row = Range("a100000").End(xlUp).RowDim i As IntegerFor i = 1 To last_rowIf Cells(i, 1).Interior.Color = 65535 And Cells(i, i).Value = "<0" ThenCells(i, 1).Value = Cells(i, 1) * negvalueElseEnd Iflast_row = last_row + 1NextEnd Sub



Thanks!!

 

[Zubair Muhammad]

Hi Sandie,

Try this. Modify the range("A1:A15") according to your situation

Sub changevalues()
Dim cell As Range
For Each cell In Range("A1:A15")
If cell.Interior.Color = 65535 Or cell < 0 Then
cell = cell * -1
End If
Next
End Sub

 

[Sandie1712]

Thanks for your kind reply Zubair!!

Two things which i can see in your provided code

1) it has OR condition whereas i am looking forward for AND condition, i shall take care of that

2) Range isn't dynamic.

One more thing in addition to your solution i would like to know the problem in my coding since that will help me improve myself.

One issue that i find in my code is

Dim last_value As "Interior" it should have been integer, but still its not working

Looking forward for solution.........

 

[Zubair Muhammad]

Hi Sandie,

Here goes the revised code

Sub changevalues()
Dim cell As Range, myrange As Range
Set myrange = Range(Range("A1"), Range("A1000000").End(xlUp))
For Each cell In myrange
If cell.Interior.Color = 65535 And cell < 0 Then
cell = cell * -1
End If
Next
End Sub

 

[Zubair Muhammad]

Hi Sandie

Here is your code with corrections.
1) You have to define negvalue as Const
2) Cells(i, i).Value = "<0" should be Cells(I,1) < 0

Sub Colormultiplewithnegative()
Const negvalue As Integer = -1
Dim last_value As Interior
last_row = Range("a100000").End(xlUp).Row
Dim i As Integer
For i = 1 To last_row
If Cells(i, 1).Interior.Color = 65535 And Cells(i, 1).Value < 0 Then
Cells(i, 1).Value = Cells(i, 1) * negvalue
Else
End If
Next
End Sub

 

[MARK858]

Just as another option as you are only using -1 to change it to a positive value...

Sub Colormultiplewithnegative()
    Dim i As Long, last_row As Long

    last_row = Cells(Rows.Count, "A").End(xlUp).Row
    
    For i = 1 To last_row

        If Cells(i, 1).Interior.Color = 65535 And Cells(i, 1).Value < 0 Then _
           Cells(i, 1).Value = Abs(Cells(i, 1))

    Next

End Sub

 

[Sandie1712]

Thanks you Zubair and Mark!!

 

[Rick Rothstein]

Here is a completely different approach (no loops) that will quickly convert negative values in Column A that are in yellow cells to positive values and it should do so rather quickly (especially if you have a lot of data to process).

Sub NegativeYellowToPlus()
  Application.FindFormat.Clear
  Application.FindFormat.Interior.Color = vbYellow
  Columns("A").Replace "-", "", xlPart, , , , True, False
  Application.FindFormat.Clear
End Sub

 

[Sandie1712]

Thanks Rick!!

Very nice code

East or West this forum is best!!