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
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
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?
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.
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