PDA

View Full Version : [SOLVED] Highlight Offset Consecutive Column Cells



dj44
12-01-2017, 07:31 AM
good friday,

I am trying to highlight my cells.

if Column A contains the word "hello" color cells B,C,D,E




Private Sub Highlight_Range()

For Each oCell In ThisWorkbook.Worksheets("test").Range("A1:A100").Cells

' How may i color cells , C,D,E,F?

If oCell = "hello" Then oCell.Offset(, 1).Interior.ColorIndex = 3

Next

End Sub



Example

A5 contains "hello"

now
A5 ,B5,C5,D5,E5 should be red

I only managed to color B

offthelip
12-01-2017, 07:42 AM
You can do this with conditional formating, select range A1 to E1 then select conditional formatting , then select "use a formula to determine which cells to format, enter the following in the box for the formula: =$A1="Hello", then select the format that you want.
Then copy the format down the rest of the worksheet

dj44
12-01-2017, 07:59 AM
hello there,

thank you for tip

oh yes i forgot about that.

now what about all the other rows in the column

Do i extend my range?

dj44
12-01-2017, 08:32 AM
I have extended the range and it seems to work so far.

let me do some more testing

p45cal
12-01-2017, 09:30 AM
If oCell = "hello" Then oCell.Resize(, 6).Interior.ColorIndex = 3 'colours A:F
If oCell = "hello" Then oCell.Offset(, 1).Resize(, 4).Interior.ColorIndex = 3 'colours B:E

dj44
12-01-2017, 10:03 AM
thank you p45cal,

The resize property is one of me least favourite things,

because i always get muddled up with it no matter how many times i try to use it elegantly in my code.

I always find it hard to see it visually.

Hence having resize amnesia :grinhalo:

but yes this does the trick,

thank you for the line of code

and great weekend all!