Find Formula Cell Quickly

[majidsiddique]

Hi All,
I need your help to find out only formula cell with highlight with color quickly. i have a large file with lot of small formula.

Thanks

 

[Fazza]

steps are in the first link posted in thread #4

repeating that here, it is, in post #6 of thread https://www.mrexcel.com/forum/excel-questions/588514-identify-cells-have-formulas.html

here is info on conditional formatting. https://support.office.com/en-us/ar...rmatting-fed60dfa-1d3f-4e13-9ecb-f1951ff89d7f
the way to apply conditional formatting has changed with Excel versions. This may not be how it is if you have an older version. The formula for your situation is per the old forum thread.
If your Excel has conditional formatting, ALT-O-D is a keyboard way to start the conditional formatting.

 

[Michael M]

It would have been so much easier to stick with Post #2...

 

[Fazza]

It would have been so much easier to stick with Post #2...

Yes. But that doesn't answer...

Hi Micheal,
Is it possible by formula?
Thanks

Season's Greetings

 

[Michael M]

Agreed.....but sometimes we don't / shouldn't need to...It seems simple enough and obvioulsy for the OP a formula solution is difficult to handle....A merry and happy back at ya!.....sitting on the golf course on the Murray...

 

[Fazza]

I thought you'd be somewhere hot.
Hope there are cool refreshments‼

 

[Michael M]

Mate...34 today....and it can get to 45 C during Jan....how much hotter do you want...

 

[majidsiddique]

hi fazza,
i got it your point,
thanks a lot, it is possible by formula.
thanks, its cheer.

 

[Fazza]

 

[majidsiddique]

hi fazza,
is it possible in excel to calculate value with background color cell via countif, sumif or vlookup,
i have attendance sheet "P" present in time, RED highlighted background color "P" for late comers.
is that possible to count RED "P" with countif, sumif ?
Thanks.

 

[Michael M]

Can't be done by native Excel function
You would need to create a UDF (code) to do this.
This function

Function Sumbycolour(CellColour As Range, SumRange As Range) 'SumbyColor(cellcolor, SumRange)
Dim myCell As Range, iCol As Integer
Dim myTotal
iCol = CellColour.Interior.ColorIndex 'gets the target colour
For Each myCell In SumRange 'looks at each cell in the range
    If myCell.Interior.ColorIndex = iCol Then
        myTotal = WorksheetFunction.Sum(myCell) + myTotal
    End If
Next myCell
Sumbycolour = myTotal
End Function

Then to count cells by the color in say A1, use

=sumbycolour(A1,A1:A500)

assuming your coloured cells are in Col "A"