Tags: cell, cellcolour, cells, color, determine, excel, font, function, microsoft, msdn, previous, red, rng, software, sum, udf, value

determine font color in cell

On Microsoft » Microsoft Excel

1,894 words with 1 Comments; publish: Wed, 04 Jun 2008 12:11:00 GMT; (30646.88, « »)

Hi,

I would like to sum the value of the cells if the font color in th

cells are red. I have try the UDF in the previous:

Function CellColour(rng As Range, Optional text As Boolean = True)

If rng.Count > 1 Then

CellColour = CVErr(xlErrValue)

Else

If text Then

CellColour = rng.Font.ColorIndex

Else

CellColour = rng.Interior.ColorIndex

End If

End If

End Function

But as the author said "it doesn't update automatically if the cel

colour is changed though.". How to make it update automactically whe

the font color changed? Or any other UDF can achieve this?

Thanks

--

Message posted from http://www.ExcelForum.com

All Comments

Leave a comment...

  • 1 Comments
    • Hi

      short question: You can't. The best you can achieve is adding the line

      application.volatile

      at the beginning of your macro.< this way it is always re-calculated

      then ANY cell changes its value. Note: format changes do NOT trigger a

      re-calculation

      --

      Regards

      Frank Kabel

      Frankfurt, Germany

      > Hi,

      > I would like to sum the value of the cells if the font color in the

      > cells are red. I have try the UDF in the previous:

      > Function CellColour(rng As Range, Optional text As Boolean = True)

      > If rng.Count > 1 Then

      > CellColour = CVErr(xlErrValue)

      > Else

      > If text Then

      > CellColour = rng.Font.ColorIndex

      > Else

      > CellColour = rng.Interior.ColorIndex

      > End If

      > End If

      > End Function

      > But as the author said "it doesn't update automatically if the cell

      > colour is changed though.". How to make it update automactically when

      > the font color changed? Or any other UDF can achieve this?

      > Thanks!

      >

      > --

      > Message posted from http://www.ExcelForum.com/

      #1; Wed, 04 Jun 2008 12:13:00 GMT