1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Need to change font color in a cell by using Before Double Click Event

Discussion in 'Business Applications' started by blass3175, Jun 18, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. blass3175

    blass3175 Thread Starter

    Joined:
    Jun 18, 2013
    Messages:
    18
    Hi all,
    I borrowed the following code from this board, and need it modified.

    Code:
    Option Explicit  
    Const MYRANGE As String = "A1:A10"  
    Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
        If Sh.Name <> "Sheet1" Then Exit Sub
        If Target.Cells.Count > 1 Then Exit Sub
        If Intersect(Target, Sh.Range(MYRANGE)) Is Nothing Then Exit Sub
        Cancel = True     
        If Target.Value = "" Then
             Target.Value = "X"
        Else
             Target.Value = ""
        End If
    End Sub
    This code places an "X" in any cell in the set range by double clicking the cell. What I need it to do is change the font color of (black) text that's already in the cell to red by double clicking the cell. And, if possible, I'd like it to toggle between about 3 or 4 other font colors as well.
    Any help would be greatly appreciated.
    Thank you!
     
  2. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    hi Ben

    Try this code to toggle through red, green, blue and yellow. If you want to change to any other colour start a "Record Macro" change the font colour and look at the resulting macro for the colour you chose.

    Code:
     Option Explicit
    Const MYRANGE As String = "A1:A10"
    Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
        If Sh.Name <> "Sheet1" Then Exit Sub
        If Target.Cells.Count > 1 Then Exit Sub
        If Intersect(Target, Sh.Range(MYRANGE)) Is Nothing Then Exit Sub
        Cancel = True
        If Selection.Font.Color = vbBlack Then
            Selection.Font.Color = vbRed
        ElseIf Selection.Font.Color = vbRed Then
            Selection.Font.Color = vbGreen
        ElseIf Selection.Font.Color = vbGreen Then
            Selection.Font.Color = vbBlue
        ElseIf Selection.Font.Color = vbBlue Then
            Selection.Font.Color = vbYellow
        Else
            Selection.Font.Color = vbBlack
        End If
    End Sub
     
  3. blass3175

    blass3175 Thread Starter

    Joined:
    Jun 18, 2013
    Messages:
    18
    XCubed,
    Thanks for your reply.
    I am mystified why I cant get this to work.
    I tried your suggestion of using a "record macro" to get the actual numerical values of the colors and replaced those--that didn't work.
    I went under macro security and made sure that macros were enabled---still didn't work.
    I tried removing the "cancel = true" statement, not sure that I needed it---still didn't work.
    I have more questions.
    1. Does the code need to exist inside a macro, or can it just be in code at the worksheet level (like I have it now)?
    2. Do I need to change the code to the workbook level? If so, what would I need to take out?
    3. Are there other settings I need to make to make the vb colors (vbRed, etc.) active?
    4. Could there be any other security, protection or other settings I need to make?
    I am using Excel 2010, on a Windows 7 Enterprise system, with Service Pack 1.
    Thank you again!
     
  4. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    The most probable problem is the worksheet name. Are you trying this in the worksheet named "Sheet 1"
    Next is the range of cells - it will only work in cells A1 to A10
    The Macro must be in the "Workbook Module"

    If you want to place the macro in the Spreadsheet module (the spreadsheet where you want this to work) you can use this (delete the original macro)



    Code:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
     
        If Target.Cells.Count > 1 Then Exit Sub
     
        If Target.Row < 11 And Target.Column = 1 Then
        Cancel = True
            If Selection.Font.Color = vbBlack Then
                Selection.Font.Color = vbRed
            ElseIf Selection.Font.Color = vbRed Then
                Selection.Font.Color = vbGreen
            ElseIf Selection.Font.Color = vbGreen Then
                Selection.Font.Color = vbBlue
            ElseIf Selection.Font.Color = vbBlue Then
                Selection.Font.Color = vbYellow
            Else
                Selection.Font.Color = vbBlack
            End If
        End If
    End Sub
    .
    here are my 2 test files - one with the macro in the Workbook module and the other in the spreadsheet module
     

    Attached Files:

  5. blass3175

    blass3175 Thread Starter

    Joined:
    Jun 18, 2013
    Messages:
    18
    XCubed,
    Thanks a million---this works!
    Now how can I get this to work using specific cells instead of a whole column?
    And what if they are cells that are not next to each other in the same column or row, but in a variety of places in the worksheet?
    Thank you!
     
  6. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    Which version of the macro are you using?
    Are the cells you want to activate random or is there some way of identifying which ones you want?
     
  7. blass3175

    blass3175 Thread Starter

    Joined:
    Jun 18, 2013
    Messages:
    18
    I am using the spreadsheet module.
    There are precise cell addresses I want to use (not random).
    Thanks!
     
  8. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    By random I meant is there a way to identify the target cells logically rather than by explicitly naming them.

    Will the set of target cells change?

    Can you post which cells you want targeted? (or a sample sheet with the cells highlighted would be preferable)
     
  9. blass3175

    blass3175 Thread Starter

    Joined:
    Jun 18, 2013
    Messages:
    18
    When you say "is there a way to identify the target cells logically" meaning the way you did it in your code using target.column and target.row, then yes. Here is what I have done so far. I have modified your code to work for cells J3 and J4. I also need it to work for cells H25 and L24. There are other cells I need it done for as well but if I can get this code right I can figure out how to modify it for the other cells. Thanks again!
     

    Attached Files:

  10. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    You can define each cell you want this way;

    Code:
    If Target.Address = "$J$3" Or Target.Address = "$J$4" Or Target.Address = "$H$24" Or Target.Address = "$L$25" Then
     
    
    Or better, (I think) is to mark the cell in a way that can be recognised. In the attached sample I shaded the cell the lightest grey and then added this IF statement.

    Code:
    If Target.Interior.TintAndShade = -4.99893185216834E-02 Then
    
    This obviously has the advantage of not having to change the macro every time you need a new cell targeted (see attached). Test it out by using the Format Painter to shade any other cell and then double click.
     

    Attached Files:

  11. blass3175

    blass3175 Thread Starter

    Joined:
    Jun 18, 2013
    Messages:
    18
    XCubed:
    It's been a few days, but I wanted to say thanks a million---the code works perfectly! I went with the "If Target.Address = "$J$3" Or If Target.Address = "$J$4" Or" code, as the form is pretty static and I don't need any new cells targeted. Thanks again!
     
  12. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1101527

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice