Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 7 of 7

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

  1. #1
    Join Date
    12th March 2012
    Location
    North Yorkshire, UK
    Posts
    6

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,220

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

    try
    VB:
    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 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    12th March 2012
    Location
    North Yorkshire, UK
    Posts
    6

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    12th March 2012
    Location
    North Yorkshire, UK
    Posts
    6

    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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,220

    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?

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    12th March 2012
    Location
    North Yorkshire, UK
    Posts
    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,220

    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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 12
    Last Post: April 3rd, 2011, 20:41
  2. Replies: 2
    Last Post: January 2nd, 2011, 19:27
  3. Replies: 4
    Last Post: September 22nd, 2009, 14:29
  4. Replies: 3
    Last Post: September 20th, 2005, 17:49
  5. Reading individual characters
    By tess457 in forum EXCEL HELP
    Replies: 2
    Last Post: February 15th, 2005, 02:57

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno