Microsoft Excel Question: Download Questions PDF
I want to count the number of cells in a range that have a particular interior color. The countif function seems like the right kind of answer but how to I return the color of the interior for each cell in a range? I would like to do something like: =countif(b1:b10, cell("interior","Red"))?
Answer:
As long as you are not using conditional formatting to turn your cells red, this will work.
It assumes you have used the red which is in the first column, third row of the interior color dropdown in Excel 97. This is known as colorindex number 3.
In Excel 95, use Insert Module. In Excel 97, use Alt-F11 to open the visual basic editor. Paste in the following code:
Public Function SumRed(Inrange As Range)
SumRed = 0
For Each cell In Inrange
If cell.Interior.ColorIndex = 3 Then
SumRed = SumRed + cell.Value
End If
Next
End Sub
Now, enter the function =Sumred(B1:B10) in your worksheet.
It assumes you have used the red which is in the first column, third row of the interior color dropdown in Excel 97. This is known as colorindex number 3.
In Excel 95, use Insert Module. In Excel 97, use Alt-F11 to open the visual basic editor. Paste in the following code:
Public Function SumRed(Inrange As Range)
SumRed = 0
For Each cell In Inrange
If cell.Interior.ColorIndex = 3 Then
SumRed = SumRed + cell.Value
End If
Next
End Sub
Now, enter the function =Sumred(B1:B10) in your worksheet.
Download MS Excel Interview Questions And Answers
PDF