CODEWITHSUNDEEP
excelexcel-formulaexcel-2010vba
user1721451
user1721451

Reputation: 247

Perform equation based on cell text colour in Excel 2010

I'm hoping someone can help - I've been tearing my hair out for hours. So I'm trying to add a variable figure to a cell based on the colour of the text in that cell. For example: enter image description here

So for example If it's an ultra tyre, 1.9 needs to be added to the pink time. In D2 83.229 would become 85.129. If it's Super (the red colour) then 1.4 would need to be added to D18.

I believe there may be a way via Name Manager, but couldn't join the dots to make it work.

Upvotes: 0

Views: 47

Answers (2)

user4039065
user4039065

Reputation:

Given your sample layout, each font colour could be picked up from the 'key' or legend and applied to the data block via an AutoFilter method. If visible numbers can be found after the color filter has been applied, a Range.PasteSpecial method can add the amount associated with the colour to the Time column.

Sub coloredTyres()
    Dim clr As Long, rng As Variant

    With Worksheets("Tyres")
        If .AutoFilterMode Then .AutoFilterMode = False
        With .Cells(1, 1).CurrentRegion
            For Each rng In .Parent.Range(.Cells(2, "H"), .Cells(2, "H").End(xlToRight))
                .AutoFilter Field:=3, Criteria1:=rng.Font.Color, _
                            Operator:=xlFilterFontColor
                If CBool(Application.Subtotal(102, .Columns(4))) Then
                    rng.Offset(1, 0).Copy
                    With Intersect(.Columns(4), _
                                   .SpecialCells(xlCellTypeVisible), _
                                   .SpecialCells(xlCellTypeConstants, xlNumbers))
                        .PasteSpecial Paste:=xlValues, operation:=xlPasteSpecialOperationAdd
                    End With
                End If
                .AutoFilter Field:=3
            Next rng
        End With
        If .AutoFilterMode Then .AutoFilterMode = False
    End With
End Sub

I've tested this on some random data but I was not about to retype your sample image out in its entirety.

Upvotes: 0

user1721451
user1721451

Reputation: 247

It looks like the easiest way will be to do a vlookup instead. More morbid curiosity.

Upvotes: 0

Related Questions