Tidying up formula

L

LynnyWeb

New Member
Joined
Feb 13, 2011
Messages
34
Hi,

I have the following formula which works fine but is there anyway I could tidy it up as it simply repeats the same process again and again? Its just that when I run the macro you see it jump through each cell and so it looks a bit clunky.


Sub Save_Holidays()
'
' Save_Holidays Macro
'
'
Range("C4").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[17]C[6],RC[4]:RC[34],FALSE)"
Range("C5").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[16]C[6],RC[4]:RC[34],FALSE)"
Range("C6").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[15]C[6],RC[4]:RC[34],FALSE)"
Range("C7").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[14]C[6],RC[4]:RC[34],FALSE)"
Range("C8").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[13]C[6],RC[4]:RC[34],FALSE)"
Range("C9").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[12]C[6],RC[4]:RC[34],FALSE)"
Range("C10").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[11]C[6],RC[4]:RC[34],FALSE)"
Range("C11").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[10]C[6],RC[4]:RC[34],FALSE)"
Range("C12").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[9]C[6],RC[4]:RC[34],FALSE)"
Range("C13").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[8]C[6],RC[4]:RC[34],FALSE)"
Range("C14").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[7]C[6],RC[4]:RC[34],FALSE)"
Range("C15").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[6]C[6],RC[4]:RC[34],FALSE)"
Range("C16").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[5]C[6],RC[4]:RC[34],FALSE)"
Range("C17").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[4]C[6],RC[4]:RC[34],FALSE)"
Range("C18").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[3]C[6],RC[4]:RC[34],FALSE)"
Range("C19").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[2]C[6],RC[4]:RC[34],FALSE)"
Range("E4").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[17]C[11],RC[2]:RC[32])"
Range("E5").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[16]C[11],RC[2]:RC[32])"
Range("E6").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[15]C[11],RC[2]:RC[32])"
Range("E7").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[14]C[11],RC[2]:RC[32])"
Range("E8").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[13]C[11],RC[2]:RC[32])"
Range("E9").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[12]C[11],RC[2]:RC[32])"
Range("E10").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[11]C[11],RC[2]:RC[32])"
Range("E11").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[10]C[11],RC[2]:RC[32])"
Range("E12").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[9]C[11],RC[2]:RC[32])"
Range("E13").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[8]C[11],RC[2]:RC[32])"
Range("E14").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[7]C[11],RC[2]:RC[32])"
Range("E15").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[6]C[11],RC[2]:RC[32])"
Range("E16").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[5]C[11],RC[2]:RC[32])"
Range("E17").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[4]C[11],RC[2]:RC[32])"
Range("E18").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[3]C[11],RC[2]:RC[32])"
Range("E19").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[2]C[11],RC[2]:RC[32])"
Range("F4").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[17]C[16],RC[1]:RC[31], FALSE)"
Range("F5").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[16]C[16],RC[1]:RC[31], FALSE)"
Range("F6").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[15]C[16],RC[1]:RC[31], FALSE)"
Range("F7").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[14]C[16],RC[1]:RC[31], FALSE)"
Range("F8").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[13]C[16],RC[1]:RC[31], FALSE)"
Range("F9").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[12]C[16],RC[1]:RC[31], FALSE)"
Range("F10").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[11]C[16],RC[1]:RC[31], FALSE)"
Range("F11").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[10]C[16],RC[1]:RC[31], FALSE)"
Range("F12").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[9]C[16],RC[1]:RC[31], FALSE)"
Range("F13").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[8]C[16],RC[1]:RC[31], FALSE)"
Range("F14").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[7]C[16],RC[1]:RC[31], FALSE)"
Range("F15").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[6]C[16],RC[1]:RC[31], FALSE)"
Range("F16").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[5]C[16],RC[1]:RC[31], FALSE)"
Range("F17").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[4]C[16],RC[1]:RC[31], FALSE)"
Range("F18").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[3]C[16],RC[1]:RC[31], FALSE)"
Range("F19").Select
ActiveCell.FormulaR1C1 = "=ColorFunction(R[2]C[16],RC[1]:RC[31], FALSE)"
Range("F20").Select
End Sub


Any help would be great thanks.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
There's no need to select

Code: 
Range("C4").FormulaR1C1 = "=ColorFunction(R[17]C[6],RC[4]:RC[34],FALSE)"
Range("C5").FormulaR1C1 = "=ColorFunction(R[16]C[6],RC[4]:RC[34],FALSE)"
 
Upvote 0
Hi, I have removed all the Select and my formula is looking exactly as yours is above but now I am getting a runtime error '438'? Object does not support this property or method?
 
Upvote 0
It's working sorry I was having a stupid moment, I took out Select and left active cell in doh!!! Thanks for your help.
 
Upvote 0
If it's always the same range, how about

Code: 
Sub Save_Holidays()
Range("C4:C19").FormulaR1C1 = "=ColorFunction(Indirect(""R[""&(21-row())&""]C[6]"",0),RC[4]:RC[34],FALSE)"
Range("E4:E19").FormulaR1C1 = "=ColorFunction(Indirect(""R[""&(21-row())&""]C[11]"",0),RC[2]:RC[32])"
Range("F4:F19").FormulaR1C1 = "=ColorFunction(Indirect(""R[""&(21-row())&""]C[16]"",0),RC[1]:RC[31], FALSE)"
End Sub

Or as an alternative, (if my guess is correct) maybe forget the macro and just press F9.

@Peter, would that require Application.Volatile in the ColorFunction UDF if not already included?
 
Upvote 0
You must log in or register to reply here.

Similar threads

Sean15
  • Question Question
Could you help improve recorded macro
2
Replies
13
Views
462
Sean15
Sean15
D
  • Solved
Insert formula in last column macro
2
Replies
13
Views
204
MARK858
MARK858
G
  • Solved
Typing out Excel formula in Excel Macro
Replies
1
Views
72
Macropod
Macropod
A
  • Solved
How use RC formulas when fill three columns
2
Replies
18
Views
233
MARK858
MARK858
Dazzawm
  • Solved
Change Range To Recorded Macro
Replies
3
Views
169
Alex Blakenburg
A

Forum statistics

Threads
1,224,598
Messages
6,179,818
Members
452,946
Latest member
JoseDavid

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top