How to multiply the values within the conditional formatting range?

[sneakikaz]

I have a big data excel file, there are many negative numbers. Now I want to highlight all the negative values and then multiply it with a specific number (for example 0.5). Could you kindly show me how to do it?
Thank you.

 

[bebo021999]

What does the "big data excel file" mean? Big range of data in single sheet, or medium range of data in lot of sheets?

 

[DHP]

I think you can use format cell to highlight the negative values and after that, you can use "IF" to multip with which number you want.
Supposedly A column is your data-> B1= If(A1<0,A1*0.5,"")

 

[bebo021999]

It took 6s for range "A1:ZZ10000" (10,000 rows * 702 columns)
Does it annoy too much?

Option Explicit
Sub multifly()
Dim i&, j&, lcell As Range, rng, n, t
t = Timer
n = 0.5
Set lcell = ActiveSheet.UsedRange.SpecialCells(xlLastCell)
rng = Range("A1", lcell).Value
For i = 1 To lcell.Row
    For j = 1 To lcell.Column
        If rng(i, j) < 0 Then rng(i, j) = rng(i, j) * n
    Next
Next
Range("A1", lcell).Value = rng
MsgBox Timer - t
End Sub

 

[sneakikaz]

It took 6s for range "A1:ZZ10000" (10,000 rows * 702 columns)
Does it annoy too much?

Option Explicit
Sub multifly()
Dim i&, j&, lcell As Range, rng, n, t
t = Timer
n = 0.5
Set lcell = ActiveSheet.UsedRange.SpecialCells(xlLastCell)
rng = Range("A1", lcell).Value
For i = 1 To lcell.Row
    For j = 1 To lcell.Column
        If rng(i, j) < 0 Then rng(i, j) = rng(i, j) * n
    Next
Next
Range("A1", lcell).Value = rng
MsgBox Timer - t
End Sub

chỉ cần chính xác với đủ dữ liệu là được, thời gian không quan trọng ạ.

 

[bebo021999]

chỉ cần chính xác với đủ dữ liệu là được, thời gian không quan trọng ạ.

Have you given it a shot?
And, does it work?

 

[sneakikaz]

Have you given it a shot?
And, does it work?

Not yet, I will try it after finishing the remaining work and tell you. Moreover, the speed is not important, I only need the accuracy and to make sure every negative number is multiplied.

 

[sneakikaz]

Have you given it a shot?
And, does it work?

Sub multifly()
Dim i&, j&, lcell As Range, rng, n, t
t = Timer
n = 0.85
Set lcell = ActiveSheet.UsedRange.SpecialCells(xlLastCell)
rng = Range("B1", lcell).Value
For i = 1 To lcell.Row
For j = 1 To lcell.Column
If -25 < rng(i, j) < -15 Then rng(i, j) = rng(i, j) * n
Next
Next
Range("A1", lcell).Value = rng
MsgBox Timer - t
End Sub

When I try with the range from -25 to -15 , it said out of range and when I pressed debug, the error appeared in the underlined are above.

 

[bebo021999]

Replace
If -25 < rng(i, j) < -15 Then
by
If rng(i, j)>-25 And rng(i,j) < -15 Then

 

[sneakikaz]

Still having the same problem, I will attach an image below for you. I want to multiply the conditional number in column B.