Define Certain Exact Range

S

sal21

Active Member
Joined
Apr 1, 2002
Messages
291
This file contain 2 sheet and a macro that colorize a column.

http://www.gssitaly.com/cartel2.zip

my problem is possible to colorize entire column saturday and sunday in grey to the last useful record is present i column A of sheet PRIMO_SEMESTRE and SECONDO_SEMESTRE? I have insert the condition to colorize until 15000.
In my case i wuold want to colorized column to end in line 811...

My idea is to make a control if in the column A is present a number, or another value....(boh!?)
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
Let's say your columns that you want to colour are A and B.

Code: 
Sub ColorFill()
  Dim c As Range
  Dim R as Long

  R = Range("B65536").End(xlUp).Row
  Range("A1:B" & R).Interior.ColorIndex = 15
End Sub
Denis
 
Upvote 0
SydneyGeek said:
Let's say your columns that you want to colour are A and B.

Code: 
Sub ColorFill()
  Dim c As Range
  Dim R as Long

  R = Range("B65536").End(xlUp).Row
  Range("A1:B" & R).Interior.ColorIndex = 15
End Sub
Denis
Click to expand...

tks for your patince, but i am very happy if you modify my code and repost here...
Click to expand...
 
Upvote 0
Hi sal,

could you post your code to the board? Most people here don't like to accept attachments because of viruses.

Denis
 
Upvote 0
SydneyGeek said:
Hi sal,

could you post your code to the board? Most people here don't like to accept attachments because of viruses.

Denis
Click to expand...

ok!, but i am professional as you, I do not allow myself to make these thanks for the note.
 
Upvote 0
sal, I had a look.
This code works with your current setup.

Code: 
Sub FillGrey()
  Dim R As Long
  Dim c As Range
  Dim wsh As Worksheet
  
  For Each wsh In ActiveWorkbook.Worksheets
    wsh.Activate
    R = Range("A65536").End(xlUp).Row
    Range(Range("F1"), Range("F1").End(xlToRight)).Select
    For Each c In Selection
      If c.Value = 7 Then 'Saturday
        c.Offset(4, 0).Resize(R - 4, 2).Interior.ColorIndex = 15
      End If
    Next c
  Next wsh
End Sub
Denis
 
Upvote 0
SydneyGeek said:
sal, I had a look.
This code works with your current setup.

Code: 
Sub FillGrey()
  Dim R As Long
  Dim c As Range
  Dim wsh As Worksheet
  
  For Each wsh In ActiveWorkbook.Worksheets
    wsh.Activate
    R = Range("A65536").End(xlUp).Row
    Range(Range("F1"), Range("F1").End(xlToRight)).Select
    For Each c In Selection
      If c.Value = 7 Then 'Saturday
        c.Offset(4, 0).Resize(R - 4, 2).Interior.ColorIndex = 15
      End If
    Next c
  Next wsh
End Sub
Denis
Click to expand...

few line, wanderfull work, great consideration for you...( professional to profesional)...
Click to expand...
 
Upvote 0
SydneyGeek said:
sal, I had a look.
This code works with your current setup.

Code: 
Sub FillGrey()
  Dim R As Long
  Dim c As Range
  Dim wsh As Worksheet
  
  For Each wsh In ActiveWorkbook.Worksheets
    wsh.Activate
    R = Range("A65536").End(xlUp).Row
    Range(Range("F1"), Range("F1").End(xlToRight)).Select
    For Each c In Selection
      If c.Value = 7 Then 'Saturday
        c.Offset(4, 0).Resize(R - 4, 2).Interior.ColorIndex = 15
      End If
    Next c
  Next wsh
End Sub
Denis
Click to expand...

My dear i have copy this 3 sheet in another wbook that contain many other sheets, the instruction:

For Each wsh In ActiveWorkbook.Worksheets

declare to open every sheets of wbook!
Is possible to set in fixed mode only PRIMO_SEMASTRE and SECONDO_SEMESTRE...
Click to expand...
 
Upvote 0
sal, try this variation...
Code: 
Sub FillGrey()
  Dim R As Long
  Dim c As Range
  Dim wsh As Worksheet
  
  For Each wsh In ActiveWorkbook.Worksheets
    If wsh.Name = "PRIMO_SEMESTRE" Or wsh.Name = "SECONDO_SEMESTRE" Then
      wsh.Activate
      R = Range("A65536").End(xlUp).Row
      Range(Range("F1"), Range("F1").End(xlToRight)).Select
      For Each c In Selection
        If c.Value = 7 Then 'Saturday
          c.Offset(4, 0).Resize(R - 4, 2).Interior.ColorIndex = 15
        End If
      Next c
    End If
  Next wsh
End Sub
Denis
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Format & Protect range of cells using VBA
Replies
7
Views
318
Gajendran Yadhav
G
U
  • Question
Information overwriting when transferring
Replies
7
Views
284
UTB
U
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
443
AndyEd
A
E
  • Question
Dynamic Task Schedule
Replies
4
Views
296
EvenS8245
E
S
  • Solved
Determine which products are made of the exact same set of materials
Replies
8
Views
807
Sajinjs
S

Forum statistics

Threads
1,215,375
Messages
6,124,592
Members
449,174
Latest member
chandan4057

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