If, Then function based on a cell format

[BMcHale]

Hi All,

I'm looking for a formula that will return a desired output based on the formatting of a cell, specifically the cell colour.

For instance if the cell is green, then return the value of the cell.

As far as I'm aware it seems that this can only be done using VBA programming, if so could anyone please walk me through it?

Thanks,

Billy

 

[Scott T]

Something like this should work. vbGreen is a specific green. If you want you can set the color by color number or RGB values.

http://dmcritchie.mvps.org/excel/colors.htm

Sub ifcolor()
If Range("A1").DisplayFormat.Interior.Color = vbGreen Then
    MsgBox "A1 is green"
Else
    MsgBox "A1 is NOT greent"
End If

End Sub

 

[JugglerJAF]

I wrote a custom function to return the colorindex value of a cell to solve a problem a colleague had with their file that was virtually identical to yours.

Function COLORINDEX(c)
    'NB: does NOT recalculate when colour is changed.  Must press F9 to recalc manually
    If c.Interior.COLORINDEX = -4142 Then
        COLORINDEX = 0
    Else
        COLORINDEX = c.Interior.COLORINDEX
    End If
End Function

You can use this, and then apply your If/Then formula based on the value returned by the function. The syntax of the formula would be:
=COLORINDEX(A1)

Please be aware that if you change the colour format of a cell, this function will not recalculate automatically. You must press F9 to force a manual recalculation.

It's generally a really bad idea to have entries highlighted by formatting/colour alone. It's much better to have a "status" column that returns a value (can be text or numeric as required), and then to use that value as the basis of a conditional format rather than trying to base it on the just the colour.

 

[BMcHale]

Hi Guys,

Thanks for the help, think I'm gonna spend a couple days to finally figure out VBA

Thanks again,
Billy

 

[BMcHale]

I wrote a custom function to return the colorindex value of a cell to solve a problem a colleague had with their file that was virtually identical to yours.

Function COLORINDEX(c)
    'NB: does NOT recalculate when colour is changed.  Must press F9 to recalc manually
    If c.Interior.COLORINDEX = -4142 Then
        COLORINDEX = 0
    Else
        COLORINDEX = c.Interior.COLORINDEX
    End If
End Function

You can use this, and then apply your If/Then formula based on the value returned by the function. The syntax of the formula would be:
=COLORINDEX(A1)

Please be aware that if you change the colour format of a cell, this function will not recalculate automatically. You must press F9 to force a manual recalculation.

It's generally a really bad idea to have entries highlighted by formatting/colour alone. It's much better to have a "status" column that returns a value (can be text or numeric as required), and then to use that value as the basis of a conditional format rather than trying to base it on the just the colour.

Hi,

Is there any way in which this could be done over a range or would that be impossible for this code?

Thanks,
Billy

 

[Scott T]

What would you want to do with the range? Do you want yes every cell is green or there is at least one cell that is green?

 

[BMcHale]

Hi, I'd be after something like this;

Where the color index of green is 14 any yellow is 6

=SUMPRODUCT((COLORINDEX(A1:A5)=14)*(B1:B5))
Where
[TABLE="width: 500"]
<tbody>[TR]
[TD](Green)[/TD]
[TD]15
[/TD]
[/TR]
[TR]
[TD](Green)[/TD]
[TD]27[/TD]
[/TR]
[TR]
[TD](Yellow)[/TD]
[TD]62[/TD]
[/TR]
[TR]
[TD](Green)[/TD]
[TD]38[/TD]
[/TR]
[TR]
[TD](Yellow)[/TD]
[TD]47[/TD]
[/TR]
</tbody>[/TABLE]

So
=SUMPRODUCT(({14,14,6,14,6}=14)*{15,27,62,38,47})
=SUMPRODUCT({1,1,0,1,0}*{15,27,62,38,47})
=SUM(15,27,0,38,0)
=80

Thanks,
Billy