# 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"``
Top Contributor Asked on January 12, 2017 in

Just edit that part

```rng.Address(, , , True)
```
Expert Answered on January 13, 2017.

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
End If
Next cel
End Sub
```
Expert Answered on January 13, 2017.

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

Top Contributor Answered on January 13, 2017.

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?

Expert Answered on January 13, 2017.

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

Top Contributor Answered on January 13, 2017.

Expert Answered on January 13, 2017.
Top Contributor Answered on January 13, 2017.

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
```

Expert Answered on January 13, 2017.

Application defined or Object defined error in line –
`Sheets(``"WBR45"``).Range(``"AE103"``).Formula = ``"=PERCENTILE.INC("` `& rng.Address & ``",99%)*24"`

Top Contributor Answered on January 13, 2017.

I have edited the code. Check the new one

Expert Answered on January 13, 2017.

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

Top Contributor Answered on January 13, 2017.

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

Top Contributor Answered on January 13, 2017.
• ## Found this useful?

Please share using the share button above.