How do I use conditional formatting in Excel to find highlighted cells?

Question

I have an Excel spreadsheet where a cell will get a value when a part is ordered and will be high lighted when the part is available to ship. I want to add up the values in the high lighted cells. Is this possible?

Answer 1

yes.....Just ensure that you are using the right Formatting...also see the help menu of windows....

Answer 2

there's not any way to do a "sum" based on the format of a cell.  You'll have to rethink how you can do this.  But, since you've apparently got some kind of "condition" that determines whether or not the cell is highlighted, you could use the same condition in a SUMIF formula.  The basic syntax of it is

=SUMIF([RANGE OF DATA], [CRITERIA], [RANGE TO SUM])

so let's say your range is A1 to B1000 and your criteria is that any cell in A greater than 0 and you want to sum up the values in column B.

you could do

=SUMIF(A1:B1000, ">0", B1:B1000)

this will look at anything in column A, and if it's greater than 0, sum the value from column B

Answer 3

You can 'Sumif highlighted' in a VBA macro this way.   This macro will sum all the cells highlighted Light Green in Column B, starting in row one, and place the total in cell C1.

Open your workbook.

Change both 'B' references in Line 4 of the macro below to the column letter your highlighted cells are.  

Change the row reference in 'B1' to whatever your starting row is, if it is not row 1.  

For example, if you were starting in row 5 in column C then Line 4 should read:

Set rng = Range("C5:" & Range("C65536"). _

This macro uses 'light green' as the highlight color, which is colorindex 35 (see Line 8).  Other index numbers are:

1 = Black,  6 = Bright Yellow,  3 = Bright Red,  36 = Light Yellow,

37 = Pale Blue,  15 = Light Gray, 40 = Tan, 10 = Green.

Change the 35 in Line 8 to one of the above numbers for summing purposes, if Light Green is not your color.  If you need a colorindex number for a different color please advise.

Change 'C1' in Line 12 to the cell reference you want the total to appear in.

Then, copy this macro to the clipboard:

Sub SumIf_Highlighted()

Dim rng As Range

Dim Ttl

Set rng = Range("B1:" & Range("B65536"). _

End(xlUp).Address(0, 0))

For Each cell In rng

cell.Select

If ActiveCell.Interior.ColorIndex = 35 Then

Ttl = Ttl + ActiveCell.Value

End If

Next

Range("C1").Value = Ttl

End Sub

Next, press ALT + F11

Insert  >  Module

Paste the macro into the Module area to the right.

Close back to Excel.

Go to Tools  >  Macro  > Macros

Highlight the SumIf_Highlighted macro, if it is not already highlighted.

Click 'Options'

Select a letter to be used as a keyboard shortcut.

Close back to Excel.

Now, to sum the column based on colorindex, press CTRL + your letter and the total will appear in the cell you have chosen.