+ Reply to Thread
Results 1 to 6 of 6

Change cell color based on justification and strikethrough

  1. #1
    Registered User
    Join Date
    01-31-2012
    Location
    Wisconsin, United States
    MS-Off Ver
    Excel 2011 Mac
    Posts
    4

    Change cell color based on justification and strikethrough

    I'm trying to write a VBA script that will look at a selected cell range, in this case E5:AS35 and change the cell background color based on the following:

    1. If data in cell is right-justified, the cell will be red (color 3).
    2. If data in cell is left-justified, the cell will be green (color 4).
    3. If data in cell has strikethrough, irregardless of justification, the cell will be white (color 2).

    I would imagine the code will require a manual sheet calculation based on UDF, but I would rather it be that than a macro I have to run. I'm not having any luck getting this to go, any suggestions?

  2. #2
    Registered User
    Join Date
    01-31-2012
    Location
    Wisconsin, United States
    MS-Off Ver
    Excel 2011 Mac
    Posts
    4

    Re: Change cell color based on justification and strikethrough

    The best I've been able to get is two separate macros:

    Sub JustColor()
    Dim Cell As Range
    Dim Rng1 As Range
    Range("E5:AS35").Select
    Set Rng1 = Selection
    For Each Cell In Rng1
    Select Case Cell.HorizontalAlignment
    Case Is = xlRight
    Cell.Interior.ColorIndex = 3
    Case Is >= xlLeft
    Cell.Interior.ColorIndex = 4
    End Select
    Next
    End Sub

    Sub StrikeColor()
    Dim Cell As Range
    Dim Rng1 As Range
    Range("E5:AS35").Select
    Set Rng1 = Selection
    For Each Cell In Rng1
    Select Case Cell.Font.StrikeThrough
    Case Is = True
    Cell.Interior.ColorIndex = 2
    End Select
    Next
    End Sub

    If I run either macro separately, it works. I don't know how to merge the two of them together or get them to run when I calculate the sheet, rather than run as a macro.

  3. #3
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Change cell color based on justification and strikethrough

    Something like
    Please Login or Register  to view this content.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  4. #4
    Registered User
    Join Date
    01-31-2012
    Location
    Wisconsin, United States
    MS-Off Ver
    Excel 2011 Mac
    Posts
    4

    Re: Change cell color based on justification and strikethrough

    Thanks mikerickson, that helped a lot. The OneCell.StrikeThrough gave me an error, I added Font. to the beginning of it. Here's what I modified it to:

    Sub ColorStrike()
    Dim oneCell As Range

    For Each oneCell In Range("E5:AS35")
    If oneCell.Font.StrikeThrough Then
    oneCell.Interior.ColorIndex = 2
    ElseIf oneCell.HorizontalAlignment = xlRight Then
    oneCell.Interior.ColorIndex = 46
    ElseIf oneCell.HorizontalAlignment = xlLeft Then
    oneCell.Interior.ColorIndex = 43
    ElseIf oneCell.HorizontalAlignment = xlCenter Then
    oneCell.Interior.ColorIndex = 2
    End If
    Next oneCell

    End Sub

    I can run it as a macro manually. I would prefer to have it run somehow if I hit the calculate now button as I have other actions run then, but I don't know how to code that. I have seen other macros coded to auto-run when the file opens, but that doesn't really do me any good. I would just like the ability to hit calculate now and have it color the cells. Is there any way to do that?

  5. #5
    Registered User
    Join Date
    01-31-2012
    Location
    Wisconsin, United States
    MS-Off Ver
    Excel 2011 Mac
    Posts
    4

    Re: Change cell color based on justification and strikethrough

    Got it!

    Private Sub Worksheet_Calculate()
    ColorStrike
    End Sub

    Sub ColorStrike()
    Dim oneCell As Range

    For Each oneCell In Range("E5:AS35")
    If oneCell.Font.StrikeThrough Then
    oneCell.Interior.ColorIndex = 2
    ElseIf oneCell.HorizontalAlignment = xlRight Then
    oneCell.Interior.ColorIndex = 46
    ElseIf oneCell.HorizontalAlignment = xlLeft Then
    oneCell.Interior.ColorIndex = 43
    ElseIf oneCell.HorizontalAlignment = xlCenter Then
    oneCell.Interior.ColorIndex = 2
    End If
    Next oneCell

    End Sub

  6. #6
    Valued Forum Contributor marreco's Avatar
    Join Date
    07-02-2011
    Location
    Brazil
    MS-Off Ver
    Excel 2010
    Posts
    1,862

    Re: Change cell color based on justification and strikethrough

    Please use the tags around the code!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1