Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 19-11-2009
Member
 
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
Reply With Quote
  #2  
Old 19-11-2009
Member
 
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
Reply With Quote
  #3  
Old 19-11-2009
Member
 
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?
Reply With Quote
  #4  
Old 19-11-2009
Member
 
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.
Reply With Quote
  #5  
Old 19-11-2009
Member
 
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
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


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


All times are GMT +5.5. The time now is 08:14 AM.