Vba with find and offset

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 10:02 PM GMT

QuestionEdit

Hi Ken. I have a question, I think I got the basics here but I'm having trouble putting it all together. I have a spreadsheet that contains about 200 rows of data. In Column G there is a value = Sub Total (two times) and then Total (once). What I'm looking to do is find these values and when found, to offset over to column K and unshade that cell. So if Subtotal is found in G120, then K120 which is shaded, is changed to interior.colorindex = xlnone. Then find the next Sub Total, do the same, then find theTotal, say in G130 and unshade k130...

AnswerEdit

Jeff

Rather than code to look for things and offset and adjust the color, I believe you can best accomplish this task with Conditional Formatting.

You will just need to add a rule, based on a formula. The formula will be something like

=(cell offset 3 columns to the left)="Sub Total"

then set the format to be what you want.

Conditional formatting requires a good understanding of absolute and relative cell references, but, once you have it down, it is incredibly powerful.

Advertisement

©2024 eLuminary LLC. All rights reserved.