Easier way to write it?

E

excelis

New Member
Joined
Jun 3, 2010
Messages
38
Hello All

I have a scripting which i was wondering whether there is an easier way to write it, for example with an interval. I have tried many ways to write, with For....Next, Do... Loop Until e.t.c., it but it seems that something is always going wrong, like with where is should put the Exit For e.t.c. I should say that this is only a part from a bigger macro which already has an interval and if full of if statements. The total macro seems like that

Code: 
For h=1 to 8000

If.......Then
   If....Then
   ElseIf  ....Then
   End If
ElseIf ..... Then
   If....Then
   ElseIf  ....Then
   End If
..........
.........
End If
Next h
So what i want is to try and make it simpler so that it will be more easily executed

My scripting is:

Code: 
ElseIf minval = 0 And minvalnext <> 0 Then
     If RowCount <> 6 Then
        If ActiveCell.Offset(1, 0) = 0 Then
            ActiveCell.Offset(1, 0).Range("A1").Select
             With Selection.Interior
              .Pattern = xlSolid
              .PatternColorIndex = xlAutomatic
              .Color = 65535
              .TintAndShade = 0
              .PatternTintAndShade = 0
          End With
        ElseIf ActiveCell.Offset(1, 1) = 0 Then
            ActiveCell.Offset(1, 1).Range("A1").Select
            With Selection.Interior
              .Pattern = xlSolid
              .PatternColorIndex = xlAutomatic
              .Color = 65535
              .TintAndShade = 0
              .PatternTintAndShade = 0
            End With
        ElseIf ActiveCell.Offset(1, -1) = 0 Then
             ActiveCell.Offset(1, -1).Range("A1").Select
              With Selection.Interior
              .Pattern = xlSolid
              .PatternColorIndex = xlAutomatic
              .Color = 65535
              .TintAndShade = 0
              .PatternTintAndShade = 0
             End With
        ElseIf ActiveCell.Offset(1, 2) = 0 Then
            ActiveCell.Offset(1, 1).Range("A1").Select
             With Selection.Interior
              .Pattern = xlSolid
              .PatternColorIndex = xlAutomatic
              .Color = 65535
              .TintAndShade = 0
              .PatternTintAndShade = 0
             End With
        ElseIf ActiveCell.Offset(1, -2) = 0 Then
            ActiveCell.Offset(1, -1).Range("A1").Select
              With Selection.Interior
              .Pattern = xlSolid
              .PatternColorIndex = xlAutomatic
              .Color = 65535
              .TintAndShade = 0
              .PatternTintAndShade = 0
              End With
        ElseIf ActiveCell.Offset(1, 3) = 0 Then
            ActiveCell.Offset(1, 1).Range("A1").Select
             With Selection.Interior
              .Pattern = xlSolid
              .PatternColorIndex = xlAutomatic
              .Color = 65535
              .TintAndShade = 0
              .PatternTintAndShade = 0
             End With
        ElseIf ActiveCell.Offset(1, -3) = 0 Then
            ActiveCell.Offset(1, -1).Range("A1").Select
             With Selection.Interior
              .Pattern = xlSolid
              .PatternColorIndex = xlAutomatic
              .Color = 65535
              .TintAndShade = 0
              .PatternTintAndShade = 0
             End With
        ElseIf ActiveCell.Offset(1, 4) = 0 Then
            ActiveCell.Offset(1, 1).Range("A1").Select
             With Selection.Interior
              .Pattern = xlSolid
              .PatternColorIndex = xlAutomatic
              .Color = 65535
              .TintAndShade = 0
              .PatternTintAndShade = 0
           End With
        ElseIf ActiveCell.Offset(1, -4) = 0 Then
            ActiveCell.Offset(1, -1).Range("A1").Select
             With Selection.Interior
              .Pattern = xlSolid
              .PatternColorIndex = xlAutomatic
              .Color = 65535
              .TintAndShade = 0
              .PatternTintAndShade = 0
             End With
       EndIf
   EndIf
Thank you for your assistance!!
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
Learn the SELECT CASE construct - ideal for multiple conditions:-
Code: 
[FONT=Courier New][SIZE=1]    [COLOR=red]  Select Case True[/COLOR][/SIZE][/FONT]

[FONT=Courier New][SIZE=1]       [COLOR=red] Case ActiveCell.Offset(1, 0) = 0
[/COLOR]          ActiveCell.Offset(1, 0).Range("A1").Select
          With Selection.Interior
           .Pattern = xlSolid
           .PatternColorIndex = xlAutomatic
           .Color = 65535
           .TintAndShade = 0
           .PatternTintAndShade = 0[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]          End With[/SIZE][/FONT]

[FONT=Courier New][SIZE=1]        [COLOR=red]Case ActiveCell.Offset(1, 1) = 0
[/COLOR]          ActiveCell.Offset(1, 1).Range("A1").Select
          With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
            .PatternTintAndShade = 0
          End With[/SIZE][/FONT]

[FONT=Courier New][SIZE=1]       [COLOR=red] Case ActiveCell.Offset(1, -1) = 0
[/COLOR]          ActiveCell.Offset(1, -1).Range("A1").Select
          With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
            .PatternTintAndShade = 0
          End With[/SIZE][/FONT]

[FONT=Courier New][SIZE=1]        [COLOR=red]Case ActiveCell.Offset(1, 2) = 0
[/COLOR]          ActiveCell.Offset(1, 1).Range("A1").Select
          With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
            .PatternTintAndShade = 0
          End With[/SIZE][/FONT]

[FONT=Courier New][SIZE=1]        [COLOR=red]Case ActiveCell.Offset(1, -2) = 0
[/COLOR]          ActiveCell.Offset(1, -1).Range("A1").Select
          With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
            .PatternTintAndShade = 0
          End With[/SIZE][/FONT]

[FONT=Courier New][SIZE=1]        [COLOR=red]Case ActiveCell.Offset(1, 3) = 0
[/COLOR]          ActiveCell.Offset(1, 1).Range("A1").Select
          With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
            .PatternTintAndShade = 0
          End With[/SIZE][/FONT]

[FONT=Courier New][SIZE=1]        [COLOR=red]Case ActiveCell.Offset(1, -3) = 0
[/COLOR]          ActiveCell.Offset(1, -1).Range("A1").Select
          With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
            .PatternTintAndShade = 0
          End With[/SIZE][/FONT]

[FONT=Courier New][SIZE=1]        [COLOR=red]Case ActiveCell.Offset(1, 4) = 0
[/COLOR]          ActiveCell.Offset(1, 1).Range("A1").Select
          With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
            .PatternTintAndShade = 0
          End With[/SIZE][/FONT]

[FONT=Courier New][SIZE=1]        [COLOR=red]Case ActiveCell.Offset(1, -4) = 0
[/COLOR]          ActiveCell.Offset(1, -1).Range("A1").Select
           With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
            .PatternTintAndShade = 0
          End With
[/SIZE][/FONT]
[FONT=Courier New][SIZE=1][COLOR=red]       End Select[/COLOR][/SIZE][/FONT]
 
Upvote 0
this part
Code: 
[SIZE=1]With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
            .PatternTintAndShade = 0
End With[/SIZE]

is a repetition - so put in a private sub and the make a call to it.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Question Question
=Sum in for Color cells in Column K
Replies
2
Views
193
zone709
Z
C
  • Solved
Protecting all cells in a sheet based on .TintAndShade
Replies
4
Views
101
Peter_SSs
Peter_SSs
K
  • Solved
formating question
Replies
3
Views
324
Micron
M
B
  • Solved
Object required error
Replies
2
Views
162
Boston_Paul
B
M
  • Question Question
Highlighting certain cells after appling filter
Replies
2
Views
370
Motoracer88
M

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