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