Announcement

Collapse
No announcement yet.

VBA macro to format individual characters in a cell depending on certain conditions

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • VBA macro to format individual characters in a cell depending on certain conditions

    Hello

    I have been asked to modify certain cells in a report so that individual characters change colour depending on certain conditions.
    Each cell represents a group of actions, each action has a number and at the time of producing the report will be in a certain state of readiness red, amber, green or unclassified. I would like the individual characters to be formatted in the colour that reflects this state of readiness.

    An example spreadsheet is attached. The colours of the characters in the desired output column in the "Output" tab are dependent on the status colours in the "Core" tab.

    Would be greatly appreciated if someone could get their brains round this one and come up with a workable solution!
    Attached Files

  • #2
    Re: VBA macro to format individual characters in a cell depending on certain conditio

    try
    Code:
    Sub test()
    Dim dic As Object, r As Range, temp As Long, rng As Range, m As Object
    Set dic = CreateObject("Scripting.Dictionary")
    With Sheets("core")
        For Each r In .Range("a2", .Range("a" & Rows.Count).End(xlUp))
            temp = r(, 2).Interior.ColorIndex
            If temp = -4142 Then temp = xlAutomatic
            dic(CStr(r.Value)) = temp
        Next
    End With
    With Sheets("output")
        Set rng = .Range("b3", .Range("b" & Rows.Count).End(xlUp))
        With CreateObject("VBScript.RegExp")
            .Global = True
            .Pattern = "\d+"
            For Each r In rng
                If .test(r.Value) Then
                    For Each m In .Execute(r.Value)
                        If dic.exists(m.Value) Then
                            r.Characters(m.firstindex + 1, m.Length).Font.ColorIndex = dic(m.Value)
                        End If
                    Next
                End If
            Next
        End With
    End With
    Set dic = Nothing
    Set rng = Nothing
    End Sub

    Comment


    • #3
      Re: VBA macro to format individual characters in a cell depending on certain conditio

      thank you, that has worked. Just need to spend a few minutes looking at the macro to understand what's going on.

      Comment


      • #4
        Re: VBA macro to format individual characters in a cell depending on certain conditio

        Have now made some modifications to the original file insofar the coloured cells are now driven by conditional formatting rather than simply filling them with a colour. When conditional formatting is applied then the macro doesn't seem to work.
        Any clues?
        Thank you

        Comment


        • #5
          Re: VBA macro to format individual characters in a cell depending on certain conditio

          If the cells is colored via Conditional formatting, it can not read the color from the cell and I don't see any change to your original file, means no conditional format.

          What are the conditions?

          Comment


          • #6
            Re: VBA macro to format individual characters in a cell depending on certain conditio

            revised file attached, please see col C in "Core" tab for conditions
            thanks
            Attached Files

            Comment


            • #7
              Re: VBA macro to format individual characters in a cell depending on certain conditio

              If you want it flexible as you change conditions or color, I will not do it. It will be a too much work.

              see http://www.cpearson.com/excel/cfcolors.htm

              Comment

              Working...
              X