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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
For starters you can stop selecting:

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

HTH,
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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")
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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?
 
Upvote 0
My code should work in all files, as long as that sheet is part of the active workbook.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

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