Shorter VBA version to do the same thing?

mrexcelhelp123

New Member
Joined
Mar 10, 2010
Messages
38
HI, I have the code I'm using now which works but was wondering if someone can help me condense it to do the same thing: THANKS!

Code:
Sub Modify_Instr_Tab()
 
Dim ws As Worksheet
 
'Assigns a due date to cell D10
Worksheets("Instructions").Range("D10").Select
ActiveCell.Formula = "5/28/2010"
 
'Assigns another due date to cell E10 if applicable
Worksheets("Instructions").Range("E10").Select
ActiveCell.Value = "NO UPDATE REQUIRED"
 
'Shades range A10:E10
Range("A10:E10").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 49407
.TintAndShade = 0
.PatternTintAndShade = 0
End With
 
For Each ws In Sheets(Array("Instructions"))
'Unshade filling for range A9: E9
With ws.Range("A9:E9")
.Interior.Pattern = xlNone
.Interior.TintAndShade = 0
.Interior.PatternTintAndShade = 0
 
'Clear contents for range D9:E9
ws.Range("D9:E9").ClearContents 
End With
Next ws
End Sub
 
Last edited by a moderator:

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
For starters you can stop selecting:

Worksheets("Instructions").Range("D10").Value = "5/28/2010"

HTH,
 

mrexcelhelp123

New Member
Joined
Mar 10, 2010
Messages
38
I'm new at VBA so I'm still learning and any help is appreciated, remember those days before you knew what you know today?
 

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,964
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
A suggestion:

Code:
Sub Modify_Instr_Tab()
    With Sheets("Instructions")
        'Assigns due dates
        .[D10:E10] = Array("5/28/2010", "NO UPDATE REQUIRED")
        'Shades range A10:E10
        With .[A10:E10].Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 49407
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        'Unshade filling for range A9:E9
        With .[A9:E9].Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        .[D9:E9] = ""
    End With
End Sub

Wigi
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650

ADVERTISEMENT

Code:
Worksheets("Instructions").Range("D10").Select
ActiveCell.Formula = "5/28/2010"

I don't think that this will work if Instructions is not the active sheet.

Try

Code:
Worksheets("Instructions").Range("D10").value = datevalue("5/28/2010")
 

mrexcelhelp123

New Member
Joined
Mar 10, 2010
Messages
38
Thanks Wigi, works like a charm and I learned something new! Quick question, what if I would like to do the same thing as the below but to other cells in the same worksheet, for example [A20:C20]? Do I repeat the same code, but just select difference cells?

With .[A9:E9].Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
 

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,964
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

Yes, altough repeating code is often the result of bad coding. Rather, extend the range, use subroutines, copy a (hidden) cell with the correct formatting, etc.

Again, please use code tags.
 
Last edited:

mrexcelhelp123

New Member
Joined
Mar 10, 2010
Messages
38
Thanks VoG for jumping in. It does work even though I'm on another tab and the Instructions tab is not active. But it doesn't work on all open workbooks. I have several workbooks with the same Instructions tab that I need same changes to be made to. Do you know how I can modify the code so the macro runs on all open workbooks?
 

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,964
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
My code should work in all files, as long as that sheet is part of the active workbook.
 

Forum statistics

Threads
1,148,393
Messages
5,746,437
Members
424,018
Latest member
dionlkk6

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
Top