How to differentiate Cells of excel using color or any units ??

[Jyotirmaya]

How to differentiate Cells of excel using color or any units ??

For example in Row O1:O9 I have values in Dollar & in O10 onwards I have values in Rupees or something. There are multiple columns and rows where such data are stored in between randomly. If Dollar unit cells font color are Red and other Rupees unit cell are Black color. How can I get the total of the both at the below of the column.

I have changed the font color, what are the other ways to do so ??

When O1:O9 selected the Sum showing as 54 but when O1:O10 selected the sum showing 104, but units are different, it should be 54$+Rs50

I want the total of O1:O9 different as the units are different and the total of those having font in different below to the column without filtering the data.

 

[Eric W]

There is no built-in formula that can determine if a cell has a colored font or not. You could theoretically create a UDF that checks for color and sums up the column that way, but that's a bit risky. If you use Conditional Formatting it wouldn't work.

I don't know how many columns you have, or how the red/black font is applied, but one option is to have a helper column next to each column you want to sum, with a D or an R (for dollars and rupees) next to the value. Then you could create the 2 sums quite easily with a SUMIF formula.

Let me know if either of those options will work for you.

 

[Jyotirmaya]

There is no built-in formula that can determine if a cell has a colored font or not. You could theoretically create a UDF that checks for color and sums up the column that way, but that's a bit risky. If you use Conditional Formatting it wouldn't work.

I don't know how many columns you have, or how the red/black font is applied, but one option is to have a helper column next to each column you want to sum, with a D or an R (for dollars and rupees) next to the value. Then you could create the 2 sums quite easily with a SUMIF formula.

Let me know if either of those options will work for you.

I am not using conditional formatting there are 10 to 15 columns where Red colored font values are scattered in various columns and there are more than 1000 rows, can you please let me know the UDF

 

[Eric W]

I don't know what shade of red you're using, but black has a colorindex of 1, so I wrote the UDF to look for "Black" (colorindex=1) or "Other" (anything else). Given a range like this:

Excel 2010

	G

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]37[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]18[/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G12[/TH]
[TD="align: left"]=colorsum(G1:G10,"Black")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G13[/TH]
[TD="align: left"]=colorsum(G1:G10,"Other")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



you can see how to code the UDF on your worksheet. To create the UDF:

1) Open your workbook
2) Press Alt-F11 to open the VBA editor
3) From the menu, select Insert --> Module
4) On the sheet that opens up, paste this code:

Public Function ColorSum(ByVal target As Range, ByVal MyColor As String)

    BlackSum = 0
    OtherSum = 0
    For Each cel In target
        If IsNumeric(cel.Value) Then
            If cel.Font.ColorIndex = 1 Then
                BlackSum = BlackSum + cel.Value
            Else
                OtherSum = OtherSum + cel.Value
            End If
        End If
    Next cel
    
    ColorSum = IIf(LCase(MyColor) = "black", BlackSum, OtherSum)
        
End Function

5) Close the VBA window
6) Test the UDF on your worksheet.

Let me know how that works.

 

[Jyotirmaya]

I don't know what shade of red you're using, but black has a colorindex of 1, so I wrote the UDF to look for "Black" (colorindex=1) or "Other" (anything else). Given a range like this:

Excel 2010

	G

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]37[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]18[/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]G12[/TH]
[TD="align: left"]=colorsum(G1:G10,"Black")[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]G13[/TH]
[TD="align: left"]=colorsum(G1:G10,"Other")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



you can see how to code the UDF on your worksheet. To create the UDF:

1) Open your workbook
2) Press Alt-F11 to open the VBA editor
3) From the menu, select Insert --> Module
4) On the sheet that opens up, paste this code:

Public Function ColorSum(ByVal target As Range, ByVal MyColor As String)

    BlackSum = 0
    OtherSum = 0
    For Each cel In target
        If IsNumeric(cel.Value) Then
            If cel.Font.ColorIndex = 1 Then
                BlackSum = BlackSum + cel.Value
            Else
                OtherSum = OtherSum + cel.Value
            End If
        End If
    Next cel
    
    ColorSum = IIf(LCase(MyColor) = "black", BlackSum, OtherSum)
        
End Function

5) Close the VBA window
6) Test the UDF on your worksheet.

Let me know how that works.

Its wonderful dear Eric its working but its not automatically updates when the number changes, can you do something for auto update ??

 

[Jyotirmaya]

yes its automatically updating when I am changing the number thank you so much Eric

 

[Eric W]

Glad to help.