Adding a formula to a cell if the cell is a color

[nharris]

Is there a way to add a function to a cell if that cell is a certain color?

So if i took these steps:

1. I made A1 "green"
2. I want D14 if it was made "green" to add this value to D14 =SUM(C14*1.0975)

Is this possible?

 

[Cubist]

You can create a function using VBA to check if the cell is green.

Insert this into a VBA module.

Function IsCellGreen(cell As Range) As Boolean
    IsCellGreen = (cell.Interior.Color = RGB(0, 255, 0))
End Function

In D14,

=IF(IsCellGreen(A1),SUM(C14*1.0975),"")

 

[nharris]

You can create a function using VBA to check if the cell is green.

Insert this into a VBA module.

Function IsCellGreen(cell As Range) As Boolean
    IsCellGreen = (cell.Interior.Color = RGB(0, 255, 0))
End Function

In D14,

=IF(IsCellGreen(A1),SUM(C14*1.0975),"")

Thanks! I will try it and let you know if it works

 

[nharris]

doesnt seem to be working. i have attached the pics of the data input that you shared with me to see if I input it correctly

 

[Cubist]

Select the green cell, ctrl + 1 - > Fill -> More colors -> Custom -> Make sure the RGB matches with the ones in VBA code.

 

[nharris]

Wow! you are awesome. Thank you soo much for the help

 

[nharris]

is there a way to make the cell auto fill when changing the color to the cell? i have to physically double click the cell and hit enter before it will update the cell

 

[Cubist]

Right-click on the tab name -> View code -> Insert this... See if this works.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1")) Is Nothing Then
        Application.EnableEvents = False
        Range("A1").Value = Range("A1").Value
        Application.EnableEvents = True
    End If
End Sub