# VBA Using formula based on color of the text

I have the below code which will calculate the formula for a particular cell range. The range mentioned in formula will have numeral which will be in red color.

What I want to do is, I want the formula to ignore the cells that has “colored text”.

`Sheets("WBR45").Range("AE103").Formula = "=PERCENTILE.INC(TP!$A$3:$D$30,99%)*24"`

Just edit that part

rng.Address(, , , True)

Not clear but if you need to detect if the cell has colored text or not you can use something like that using loops

Sub Test() Dim cel As Range For Each cel In Range("A1:A10") If cel.Font.Color <> 0 Then Debug.Print cel.Address End If Next cel End Sub

If the cell has colored text, then that cell should not be included in the formula. The cell with only black colored text should be taken for calculation

I think you have to loop the range first and store the cells ranges that match your criteria first then use this stored range in the formula

I am not sure .. Can you tell us what the formula do exactly?

The formula calculates the percentile from sheet “TP” and paste it in cell in WBR45 sheet

Can you upload sample of your workbook?

May be

Sub Test() Dim cel As Range Dim rng As Range Dim arr As Variant Dim i As Long Application.ScreenUpdating = False For Each cel In Sheets("TP").Range("A3:D30") If cel.Font.Color = 0 Then If rng Is Nothing Then Set rng = cel Else Set rng = Union(cel, rng) End If End If Next cel ReDim arr(rng.Count - 1) If Not rng Is Nothing Then For Each cel In rng arr(i) = cel i = i + 1 Next cel Sheets("TP").Range("AH1").Resize(UBound(arr) + 1).Value = Application.Transpose(arr) Set rng = Sheets("TP").Range("AH1:AH" & Sheets("TP").Cells(Rows.Count, "AH").End(xlUp).Row) Sheets("WBR45").Range("AE103").Formula = "=PERCENTILE.INC(" & rng.Address & ",99%)*24" Sheets("WBR45").Range("AE103").Value = Sheets("WBR45").Range("AE103").Value Sheets("TP").Columns("AH:AH").ClearContents End If Application.ScreenUpdating = True End Sub

Application defined or Object defined error in line –

`Sheets(`

`"WBR45"`

`).Range(`

`"AE103"`

`).Formula = `

`"=PERCENTILE.INC("`

`& rng.Address & `

`",99%)*24"`

I have edited the code. Check the new one

Awesome!! This is exactly what I wanted.

But there is a small glitch…the calculation is not correct…I mean the result in the destination is in thousands and it should not be like that..it will be a two digits and few decimals..If i apply the formula directly I’m getting the correct answer

I dont find the above line in the code..should I add this?