Results 1 to 5 of 5

Thread: Excel 2007 macro help needed

  1. #1
    Join Date
    Jun 2009
    Posts
    3,859

    Excel 2007 macro help needed

    Can you help me to use Macro in much better under Excel 2007?

    1st issue:
    I try to run a macro to change cell (entire sheet)

    2nd issue:
    I try to change the color of various cells

    1 2 3 4 5
    A JACK
    B
    C PAUL
    ,,,
    X
    if A1 = jack then A3 and A5 in red
    if A1 = "" then A3 and A5 in transparent
    if A1 = Paul then A2 and A4 in green
    valid on any column A1 to ... X1

  2. #2
    Join Date
    Nov 2008
    Posts
    1,054

    Re: Excel 2007 macro help needed

    That is the macro that corresponds to your statement, I understand (there are no lines A through X, are the columns that are identified by letters?).

    Make a right click on the sheet tab where you want the macro to run and then click on "View Code". Then copy and paste the following code:

    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    
    Dim intLine As Integer
    
    'No. current line:
    intLine = ActiveCell.Row
    
    'If the line number clicked is less than or equal to 22 (line "X")
    If intLine <= 22 Then
       'Action on the value of the first cell of the current line:
        Select Case Cells(intLine, 1)
            Case "Jack" 'If the cell value is equal to Jack:
                Cells(1, 3).Interior.Color = vbRed 'cell A3 in red
                Cells(1, 5).Interior.Color = vbRed 'cell A5 in red
            
            Case "Paul" 'If the cell value is equal to Paul:
                Cells(1, 2).Interior.Color = vbGreen 'cell A2 in green
                Cells(1, 4).Interior.Color = vbGreen 'cell A4 in green
            
            Case "" 'If the cell is empty
                Cells(1, 3).Interior.ColorIndex = xlNone 'cell A3 transparent
                Cells(1, 5).Interior.ColorIndex = xlNone 'cell A5 transparent
        End Select
    End If
    
    End Sub

  3. #3
    Join Date
    Jun 2009
    Posts
    3,859

    Re: Excel 2007 macro help needed

    Thank you for the quick response but the above code does not work. When I type "Paul" in A1, nothing happens. Is there something more to add or am I wrong somewhere?

  4. #4
    Join Date
    Nov 2008
    Posts
    1,054

    Re: Excel 2007 macro help needed

    3 tracks:
    1) you have removed the code that automatically registers after "View Code"? It is necessary that the module is empty before pasting the code.
    2) You might have putted paul instead of Paul (this macro is case sensitive)
    3) You clicked on the button "enter" and the active cell is the line below and the macro does not work (click rather than using TAB key) to move to the cell to the right of cell A1

    In the 3rd case should this case go all the list A1: A22 to update the cells. I do not have time to do yet.

  5. #5
    Join Date
    Jun 2009
    Posts
    3,859

    Re: Excel 2007 macro help needed

    Ok it works now. I put Paul in A1 and it changes the color of B1 and D1 "green" but when I put Paul in A18 it changes the color of B1 and D1 "green". I have the same color on B18 and D18. My goal is to make a mask before. So is "Paul or Jack" will be written from A1 to A1000

Similar Threads

  1. Excel 2003 Macro doesn't work in Excel 2007
    By jjaw in forum Windows Software
    Replies: 3
    Last Post: 03-01-2014, 03:28 PM
  2. Help needed to add a Chart with a macro in Excel 2007
    By Clemens in forum Windows Software
    Replies: 3
    Last Post: 19-11-2010, 12:31 AM
  3. Macro for deleting every alternate row in Excel 2007
    By Akiraa in forum Windows Software
    Replies: 3
    Last Post: 22-10-2009, 10:19 PM
  4. Set the Macro Security Level in Excel 2007
    By NIcaBoy in forum Windows Software
    Replies: 3
    Last Post: 21-04-2009, 02:04 PM
  5. Macro sum problem in excel 2007
    By Radley in forum Windows Software
    Replies: 2
    Last Post: 07-04-2009, 09:46 PM

Tags for this Thread

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
  •  
Page generated in 1,711,626,734.43273 seconds with 17 queries