Selecting a range in VBA (2002/SP3)

Topic

New Reply

Hi,

I have the following set of data in columns A and B with hundreds of rows (starting from row 2):

A B C D
1
1 test#1 yes
3 tes#2 yes
4 test#3 no
5 test#4 yes
…

The data would be initially be in columns A and B, with more data entered in pairs in other columns. In VBA , how do I:
1) Select Col A and B, and change the color in the range to red
2) Go to the first cell in the active range and offset by 3 columns, and check if there any data in activecell. If activecell is not empty then color it blue.
3) Repeat step 2, interchanging colors from green to red

Thanks.

Reply | Quote

Replies

I’m not sure I understand your question. You mention red and blue, then later on “interchanging colors from green to red”.
What you want can probably be done without code, using Conditional Formatting, but I’d like to understand what exactly you’re trying to accomplish.

Reply | Quote

A typo. I meant to write “interchanging colors from blue to red”.

What I’m trying to do is to check the worksheet for data and make it visually easier to distiguish between the various pairs of data.

Reply | Quote

Thanks, but I’m a bit dense today. Could you give some examples of what you expect the result to be? What should be red, what should be blue?

Reply | Quote

Sorry for not being clearer. I’ve attached a sample workbook. What I’m trying to do is check all worksheet in a workbook for data and if data exists colour it in order to make it easier to view.

Reply | Quote

Sorry for being so stupid, but why would you need code for this? You can simply select columns A and B in their entirety and set text color to red, then select columns D and E and select blue, etc.
This will take less than a minute and you have to do it only once.

Reply | Quote

I’m planning to write a VBA to manipulate the data pairs in each worksheet.

Reply | Quote

OK, but the coloring doesn’t depend on the data…

Reply | Quote

True, coloring is just an extra thing. The procedure from your last post is useful, thanks.

In the code that I have below, I want to go through each data pair and change some values. I start from from the pair in cell A2, and want to go to the next pair and so on. The way my code is written, I keep coming back to A2. How do I fix this?

‘ For/Next loop starts here here
Set TableRange = ActiveSheet.Range(“A2”).CurrentRegion
Set CellRange = TableRange.Cells(1, 1)
CellRange.Offset(0, 3).Activate
‘ Next

Reply | Quote

Try something like this:

Dim r As Long
Dim m As Long
Dim oCell1 As Range
Dim oCell2 As Range
‘ Last filled row in column A
m = Cells(Rows.Count, 1).End(xlUp).Row
‘ Loop through the rows, starting at row 2
For r = 2 To m
‘ Cell in column A in the “current” row
Set oCell1 = Cells(r, 1)
‘ And the cell in column D
Set oCell2 = Cells(r, 4)
‘ Do something with the cells (without selecting them)
…
Next r

Reply | Quote

Thanks Hans. That’s what I was looking for. 🙂

Reply | Quote

Does this do what you want?

Sub ColorColumns()
Dim c As Long
Dim m As Long
m = Cells.Find(What:=”*”, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
For c = 1 To m
Select Case c Mod 6
Case 1, 2
Columns©.Font.Color = vbRed
Case 4, 5
Columns©.Font.Color = vbBlue
End Select
Next c
End Sub

Reply | Quote