recording macro question

[fuerza]

how can i make macro work on different sheets and different cells .

example im going record macro then im going to change the color of cell A1 in sheet 1 then i will stop the macro then i will run the previous macro that i have recorded in sheet 2 but i want to change the color of different cell ,cell B3 sheet 2 with that macro.

but the result is cell A1 is filled not the cell B3

how can i fill cell B3 with the same macro?

thanks

 

[mumps]

If the color of A1 and B3 is the same, you just have to reference both cells in the one macro and it will change the color in both with one run.

 

[bertie]

Replace all references to, ActiveSheet", with the actual sheet name. For example:

Sub test()
   Sheets("Sheet1").Range("A1").Interior.Color = vbRed
   Sheets("Sheet1").Range("A1").Font.Color = vbWhite
   
   Sheets("Sheet2").Range("B3").Interior.Color = vbBlue
   Sheets("Sheet2").Range("B3").Font.Color = vbYellow
End Sub

 

[fuerza]

thanks got it i use the funtion relative reference


why is that when i record macro and i run it to other sheet sometime some of its parts are in the wrong place sometime it works perfectly? i use excel 2007

also when i try to record macro using filling cells with colors sometimes i got an error which need to debug like run time error 1004 object defined error?

thanks

 

[bertie]

Consider a simple example. The code generated by the macro recorder produces statements like:
ActiveSheet.Select.

If the Sheet1 tab is selected when you run the code, Sheeet1 is the ActiveSheet, and the code produces expected results.
If the Sheet2 tab is selected when the code is run, Sheet2 is the ActiveSheet, and the code produces unexpected results.

Is the code producing an error?
No! The code is doing as instructed and working on the ActiveSheet.
It is the programmer’s responsibility to be unambiguous about which worksheet the code performs actions on.
That’s why it is advisable to edit the recorder generated code and use specific referencing with regards to sheet names.

Note also in post #3. You don’t need to select a sheet/range/cell in order to use it.

Sheets, ranges and cells are objects. The cause of the, 1004 run time error, may be that the code can’t determine what it is supposed to be working on. But without seeing the code it is difficult to say.