• Ask a Question
150
Insert Image Size must be less than < 5MB.
    Ask a Question
    Cancel
    150
    More answer You can create 5 answer(s).
      Ask a Poll
      Cancel
      Top Contributor

      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"
      Asked by silverblade on January 12, 2017 in VBA.
      17 Answers
      Expert

      Just edit that part

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

      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
      
      Answered by YasserKhalil on January 13, 2017..
      Top Contributor

      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

      Answered by silverblade on January 13, 2017..
      Expert

      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?

      Answered by YasserKhalil on January 13, 2017..
      Top Contributor

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

      Answered by silverblade on January 13, 2017..
      Expert

      Can you upload sample of your workbook?

      Answered by YasserKhalil on January 13, 2017..
      Top Contributor
      Answered by silverblade on January 13, 2017..
      Expert

      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
      

       

      Answered by YasserKhalil on January 13, 2017..
      Top Contributor

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

      Answered by silverblade on January 13, 2017..
      Expert

      I have edited the code. Check the new one

      Answered by YasserKhalil on January 13, 2017..
      Top Contributor

      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

       

      Answered by silverblade on January 13, 2017..
      Top Contributor

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

      Answered by silverblade on January 13, 2017..