If cell is given background color then sum

[dog party]

Hi all. First time poster here. I've used this site for quite a while to increase my Excel abilities and I thank you all for your great insight.

This seems like it should be simple, but it has me scratching my head. Hopefully, someone can help me out.

I am looking for a formula that would multiply the quantity from one cell with the cost from another cell, based entirely on whether or not a third cell is manually given a background color (any color).

So, if my user highlights cell A2, I want the formula in cell D2 to multiply the figures in cells B2 and C2.

Thanks for any advice!

 

[Scott T]

If the color is not from conditional formatting but by a person changing the color then you can not do this by formula. You would need to use VBA.

 

[dog party]

Thanks Scott T. That's the answer I've been finding in all my research.

I am pretty new to using VBA. Is there any way to make it run automatically? My users don't have time to run the macro every time they update a cell. I was trying to stick with a formula so their line would calculate the instant they highlighted it.

 

[Computerman]

dog party,
I wasn’t taking no for an answer either. So I found a way that might work for you. In the Worksheet_selectionChange(byVal Target as range) event of the Worksheet place the following code”

If Sheet1.Range("A10").Interior.ColorIndex <> -4142 Then
    Sheet1.Range("D10").Value = Sheet1.Range("B10").Value * Sheet1.Range("C10").Value
Else
    Sheet1.Range("D10").Value = ""
End If

However, the calculation will occur once the user clicks out of the B cell.

HTH,
Computerman

 

[percom74]

My code value was wrong....

 

[percom74]

Sorry didn't see Computerman's response....

 

[dog party]

dog party,
I wasn’t taking no for an answer either. So I found a way that might work for you. In the Worksheet_selectionChange(byVal Target as range) event of the Worksheet place the following code”

If Sheet1.Range("A10").Interior.ColorIndex <> -4142 Then
    Sheet1.Range("D10").Value = Sheet1.Range("B10").Value * Sheet1.Range("C10").Value
Else
    Sheet1.Range("D10").Value = ""
End If

However, the calculation will occur once the user clicks out of the B cell.

HTH,
Computerman

Interesting! Thanks!