Calculation based on conditional formatting

[dutchmhk]

Hello All

I have been struggling with a solution to make a calculation based on a conditional formatting result

Let me explain

I have a currency row for which I have made conditional formatting so that a cell fills red whenever there is USD, SEK or NOK text in that cell. - this is working perfectly.
What I would like to do in VBA, is whenever I have a red cell, it will use the value in the cell to the left and multiply it with a cell a few columns to the right (all in the same row)
Then paste the result in the cell to the left from the initial red cell.
So if my active cell is O7 and it's red due to the cell contains "USD" then multiply the value from N7 to U7 and place the result in N7

I hope it makes a bit of sense and you guys are willing to help out

Tnx in advance

 

[Michael M]

Is this going to happen in many rows ?
Does it need to happen automatically ?
If it's a one off, try

Sub MM1()
If ActiveCell.Interior.Color = vbRed Then
Cells(7, 14).Value = Cells(7, 14).Value * Cells(7, 21).Value
End If
End Sub

 

[dutchmhk]

Is this going to happen in many rows ?
Does it need to happen automatically ?
If it's a one off, try

Sub MM1()
If ActiveCell.Interior.Color = vbRed Then
Cells(7, 14).Value = Cells(7, 14).Value * Cells(7, 21).Value
End If
End Sub

Thank you for a very quick reply

The "if activecell.interior.Color = vbred Then" does not seem to work (cell was active when I tried) but if I remove it, the calculation works perfectly

Preferably the vba code should be activated when I press a button (button is in place - only need the vba code)
and then do the calculations for each red filled cell in a range in that specific currency row
Conditional formatted row is O and the range is O4:O500

 

[Michael M]

Try using

Sub MM1()
 Dim r As Long
 For r = 4 To 500
     If Cells(r, 15).Interior.Color = vbRed Then
        Cells(r, 14).Value = Cells(r, 14).Value * Cells(r, 21).Value
    End If
Next r
End Sub

 

[Peter_SSs]

The "if activecell.interior.Color = vbred Then" does not seem to work

No, that will not work as it is testing the underlying cell colour, not any colour applied by conditional formatting. To do that you would need

If Cells(r, 15).DisplayFormat.Interior.Color = vbRed Then

However, there is no real need to check for that CF cell colour. Because the colour is applied due to the values in column O, you can just as well use those values to determine whether to multiply or not.
Further, you should be able to process the whole column at a time rather than cycling through the rows.

Give this a try with a copy of your workbook.

Sub MultiplyCells()
  With Range("N2:N" & Range("O" & Rows.Count).End(xlUp).Row)
    .Value = Evaluate(Replace("if(isnumber(find(""|""&" & .Offset(, 1).Address & "&""|"",""|USD|SEK|NOK|"")),%*" & .Offset(, 7).Address & ",if(%="""","""",%))", "%", .Address))
  End With
End Sub

If you do not have any blank cells among the data in column N, then this code could be shortened a bit.

 

[dutchmhk]

Thank you for a very quick reply

The "if activecell.interior.Color = vbred Then" does not seem to work (cell was active when I tried) but if I remove it, the calculation works perfectly

Preferably the vba code should be activated when I press a button (button is in place - only need the vba code)
and then do the calculations for each red filled cell in a range in that specific currency row
Conditional formatted row is O and the range is O4:O500

No, that will not work as it is testing the underlying cell colour, not any colour applied by conditional formatting. To do that you would need

If Cells(r, 15).DisplayFormat.Interior.Color = vbRed Then

However, there is no real need to check for that CF cell colour. Because the colour is applied due to the values in column O, you can just as well use those values to determine whether to multiply or not.
Further, you should be able to process the whole column at a time rather than cycling through the rows.

Give this a try with a copy of your workbook.

Sub MultiplyCells()
  With Range("N2:N" & Range("O" & Rows.Count).End(xlUp).Row)
    .Value = Evaluate(Replace("if(isnumber(find(""|""&" & .Offset(, 1).Address & "&""|"",""|USD|SEK|NOK|"")),%*" & .Offset(, 7).Address & ",if(%="""","""",%))", "%", .Address))
  End With
End Sub

If you do not have any blank cells among the data in column N, then this code could be shortened a bit.

Hi Peter

I changed the "N" range from N2:N500 and it just works I am super happy! Thank you guys so much for helping out.

I am completely new to VBA and after a fair amount of google and youtube I ended up with half a page of VBA code and nothing worked.
This is like two lines of code and BOOM! > Working!

Thanks again to both of you for this super super quick support.

 

[Peter_SSs]

You're welcome. Glad we could help.

 

[dutchmhk]

Hi again Peter
Would you mind helping me out with another issue involving comparing columns from two wordbooks. If so, let me know if you prefer to continue in this post or make a new one

Any help would be very much appreciated

best regards
dutch

 

[Peter_SSs]

Sounds like your new question is not closely related to this one so you should start a new thread.
I can't specifically say that I will help but I try to look at as many threads as I can and if I come across yours and think I can help, I will.

 

[dutchmhk]

Thank you - much appreciated

I'll open a thread