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]

So the working range is column B downwards?

try:

Option Explicit
Sub multifly()
Dim i&, k&, lr&, n, min As Double, max As Double, rng, arr(1 To 1000000, 1 To 2)
n = 0.5 ' the multifly factor
min = -25 'lower value
max = -15 'upper value
lr = Cells(Rows.Count, "B").End(xlUp).Row ' last row of column B
rng = Range("B1:B" & lr).Value
For i = 1 To lr
    arr(i, 1) = rng(i, 1)
    If rng(i, 1) > min And rng(i, 1) < max Then
        arr(i, 1) = rng(i, 1) * n: arr(i, 2) = "B" & i
    End If
Next
With Range("B1:B" & lr)
    .Interior.Color = xlNone
    .Value = arr
End With
For i = 1 To lr
    If arr(i, 2) <> "" Then Range(arr(i, 2)).Interior.Color = vbYellow
Next
End Sub

 

[sneakikaz]

So the working range is column B downwards?

try:

Option Explicit
Sub multifly()
Dim i&, k&, lr&, n, min As Double, max As Double, rng, arr(1 To 1000000, 1 To 2)
n = 0.5 ' the multifly factor
min = -25 'lower value
max = -15 'upper value
lr = Cells(Rows.Count, "B").End(xlUp).Row ' last row of column B
rng = Range("B1:B" & lr).Value
For i = 1 To lr
    arr(i, 1) = rng(i, 1)
    If rng(i, 1) > min And rng(i, 1) < max Then
        arr(i, 1) = rng(i, 1) * n: arr(i, 2) = "B" & i
    End If
Next
With Range("B1:B" & lr)
    .Interior.Color = xlNone
    .Value = arr
End With
For i = 1 To lr
    If arr(i, 2) <> "" Then Range(arr(i, 2)).Interior.Color = vbYellow
Next
End Sub

Thank you so much, it works perfectly. Em cảm ơn nhiều lắm ạ.

 

[sneakikaz]

So the working range is column B downwards?

try:

Option Explicit
Sub multifly()
Dim i&, k&, lr&, n, min As Double, max As Double, rng, arr(1 To 1000000, 1 To 2)
n = 0.5 ' the multifly factor
min = -25 'lower value
max = -15 'upper value
lr = Cells(Rows.Count, "B").End(xlUp).Row ' last row of column B
rng = Range("B1:B" & lr).Value
For i = 1 To lr
    arr(i, 1) = rng(i, 1)
    If rng(i, 1) > min And rng(i, 1) < max Then
        arr(i, 1) = rng(i, 1) * n: arr(i, 2) = "B" & i
    End If
Next
With Range("B1:B" & lr)
    .Interior.Color = xlNone
    .Value = arr
End With
For i = 1 To lr
    If arr(i, 2) <> "" Then Range(arr(i, 2)).Interior.Color = vbYellow
Next
End Sub

Dear bebo021999,
If now I want to multiply the data in multiple columns, such as columns B, D, F, H, J, L, N, etc. what changes should I make in your VBA code?

 

[bebo021999]

With more data to input manually, could you attach a mini sheet? Its the best i you would add "before" and "after" data

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[sneakikaz]

First, you helped me to multiply the values in columns B

 

[sneakikaz]

First, you helped me to multiply the values in column B like this with your code, in this situation, any value in the range from 0 to 1 will be doubled.

	Before			After
2400	-7.04688		2400	-7.04688
2400.04	-8.89688		2400.04	-8.89688
2400.08	-11.1188		2400.08	-11.1188
2400.12	-13.8656		2400.12	-13.8656
2400.16	-17.6656		2400.16	-17.6656
2400.2	-22.8719		2400.2	-22.8719
2400.24	-31.4469		2400.24	-31.4469
2400.28	-45.6094		2400.28	-45.6094
2400.32	-58.6031		2400.32	-58.6031
2400.36	-51.1219		2400.36	-51.1219
2400.4	-10.5844		2400.4	-10.5844
2400.44	0.80937		2400.44	1.61874
2400.48	-1.54688		2400.48	-1.54688

 

[sneakikaz]

With more data to input manually, could you attach a mini sheet? Its the best i you would add "before" and "after" data

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

Now can you please help me to multiply all the values from 0 to 10 for example (only the figures in the yellow columns) with 2?

0	46.6563	400	0.28906	800	0.44531	1200	0.36719	1600	0.3125	2000	0.29688	2400	-7.04688	2800	0.04063	3200	0.375	3600	0.14688
0.04	110.883	400.04	0.28906	800.04	0.44531	1200.04	0.375	1600.04	0.32813	2000.04	0.25781	2400.04	-8.89688	2800.04	0.0375	3200.04	0.375	3600.04	0.13437
0.08	68.0391	400.08	0.3125	800.08	0.42188	1200.08	0.35156	1600.08	0.33594	2000.08	0.25	2400.08	-11.1188	2800.08	0.02187	3200.08	0.34375	3600.08	0.10312
0.12	24.0703	400.12	0.28906	800.12	0.40625	1200.12	0.35156	1600.12	0.35156	2000.12	0.28906	2400.12	-13.8656	2800.12	0.00625	3200.12	0.35	3600.12	0.1
0.16	15.2031	400.16	0.28906	800.16	0.39063	1200.16	0.36719	1600.16	0.32813	2000.16	0.3125	2400.16	-17.6656	2800.16	-0.00625	3200.16	0.375	3600.16	0.09375
0.2	11.4531	400.2	0.25781	800.2	0.375	1200.2	0.39063	1600.2	0.3125	2000.2	0.35156	2400.2	-22.8719	2800.2	-0.00937	3200.2	0.39687	3600.2	0.1
0.24	9.44531	400.24	0.29688	800.24	0.39063	1200.24	0.36719	1600.24	0.32813	2000.24	0.3125	2400.24	-31.4469	2800.24	-0.025	3200.24	0.37188	3600.24	0.07187

 

[bebo021999]

Try this:

Option Explicit
Sub multifly()
Dim i&, j&, min As Double, max As Double, fact As Double, rng
min = 0 ' adjust to actual value
max = 10 ' adjust to actual value
fact = 2 ' adjust to actual value
rng = Cells(1).CurrentRegion.Value
For i = 1 To UBound(rng)
    For j = 2 To UBound(rng, 2) Step 2 ' loop through column B,D,F,H,...
        If rng(i, j) >= min And rng(i, j) <= max Then rng(i, j) = rng(i, j) * fact
    Next
Next
Cells(1).CurrentRegion.Value = rng
End Sub

BEFORE

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	0	46.6563	400	0.28906	800	0.44531	1200	0.36719	1600	0.3125	2000	0.29688	2400	-7.04688	2800	0.04063	3200	0.375	3600	0.14688
2	0.04	110.883	400.04	0.28906	800.04	0.44531	1200.04	0.375	1600.04	0.32813	2000.04	0.25781	2400.04	-8.89688	2800.04	0.0375	3200.04	0.375	3600.04	0.13437
3	0.08	68.0391	400.08	0.3125	800.08	0.42188	1200.08	0.35156	1600.08	0.33594	2000.08	0.25	2400.08	-11.1188	2800.08	0.02187	3200.08	0.34375	3600.08	0.10312
4	0.12	24.0703	400.12	0.28906	800.12	0.40625	1200.12	0.35156	1600.12	0.35156	2000.12	0.28906	2400.12	-13.8656	2800.12	0.00625	3200.12	0.35	3600.12	0.1
5	0.16	15.2031	400.16	0.28906	800.16	0.39063	1200.16	0.36719	1600.16	0.32813	2000.16	0.3125	2400.16	-17.6656	2800.16	-0.00625	3200.16	0.375	3600.16	0.09375
6	0.2	11.4531	400.2	0.25781	800.2	0.375	1200.2	0.39063	1600.2	0.3125	2000.2	0.35156	2400.2	-22.8719	2800.2	-0.00937	3200.2	0.39687	3600.2	0.1
7	0.24	9.44531	400.24	0.29688	800.24	0.39063	1200.24	0.36719	1600.24	0.32813	2000.24	0.3125	2400.24	-31.4469	2800.24	-0.025	3200.24	0.37188	3600.24	0.07187
Sheet1

AFTER:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	0	46.6563	400	0.57812	800	0.89062	1200	0.73438	1600	0.625	2000	0.59376	2400	-7.04688	2800	0.08126	3200	0.75	3600	0.29376
2	0.04	110.883	400.04	0.57812	800.04	0.89062	1200.04	0.75	1600.04	0.65626	2000.04	0.51562	2400.04	-8.89688	2800.04	0.075	3200.04	0.75	3600.04	0.26874
3	0.08	68.0391	400.08	0.625	800.08	0.84376	1200.08	0.70312	1600.08	0.67188	2000.08	0.5	2400.08	-11.1188	2800.08	0.04374	3200.08	0.6875	3600.08	0.20624
4	0.12	24.0703	400.12	0.57812	800.12	0.8125	1200.12	0.70312	1600.12	0.70312	2000.12	0.57812	2400.12	-13.8656	2800.12	0.0125	3200.12	0.7	3600.12	0.2
5	0.16	15.2031	400.16	0.57812	800.16	0.78126	1200.16	0.73438	1600.16	0.65626	2000.16	0.625	2400.16	-17.6656	2800.16	-0.00625	3200.16	0.75	3600.16	0.1875
6	0.2	11.4531	400.2	0.51562	800.2	0.75	1200.2	0.78126	1600.2	0.625	2000.2	0.70312	2400.2	-22.8719	2800.2	-0.00937	3200.2	0.79374	3600.2	0.2
7	0.24	18.89062	400.24	0.59376	800.24	0.78126	1200.24	0.73438	1600.24	0.65626	2000.24	0.625	2400.24	-31.4469	2800.24	-0.025	3200.24	0.74376	3600.24	0.14374
Sheet1

 

[sneakikaz]

Oh, it worked perfectly fine, much much appreciated.