Possible to highlight cells depending on value from vlook up?

[JAZZNAURA]

Hi all,

Need some help! Want to highlight cells depending on a value returned
by a vlook up formula. If a value of 6 (could be anything from 2 to 50)
is returned in cell A100 (could be any column, cell) then highlight the
next 6 cells down (also in some cases the cells above instead of
below). Can this be done with out the use of a macro? A column will
only need to be highlighted up or down. So looking for 2 formulas one
to highlight down and one to highlight up, which I can paste in every
other column.

New to excel don’t know if this is straight forward or not.

Hope I’ve explained myself clearly, any assistance would be
appreciated.

Cheers

 

[Guest]

This would require programming in VBA.

Doable, but not with a standard Excel formula.

 

[RagDyer]

Select the 6 cells that you wish to format, then:
<Format> <Conditional Format>
Change "Cell Value Is" to "Formula Is", and enter this in the box on the
right:

=A$100=6

Then click on "Format:", and choose whatever format you wish, then <OK>
<OK>.

Now, while those 6 cells are *still* selected, *double* click on the Format
Painter icon (yellow paint brush) on the tool bar, and then click in each
of the top cells of each set of 6 cells that you wish to have this same
format.
Hit <Esc> when you're finished to turn off the format painter.

 

[JAZZNAURA]

Thanks for the reply.

The formula would not give me the flexibility if the value returned was
higher or lower, would it RagDyer? Sorry if I didn’t make myself clear,
may need to highlight 6 cells of 60, depending on the value return by
vlook up formula.

Dave F, when you say vba programming, do you mean a macro? If so reason
for not using is to keep file size to minimum, hope I’m correct in my
thinking here.

Apologies if I’ve made incorrect statements here.

RayDyer, Is the formula flexible in that sense?

Dave F r u talking about a macro and would it be simple to do?

cheers again

 

[Guest]

Macros use VBA coding.

But using VBA wouldn't increase the file size appreciably, at least for what
you're trying to do. Unfortunately, the precise coding you're looking for is
out of my knowledge level.

Perhaps try RagDyer's suggestion?

 

[JAZZNAURA]

Thanks for the reply,

Tried RagDyer's suggestion works but doesn’t give me the flexibility I
need. Would have to format a lot of cells on a daily bases as most have
different values. ABOUT 4000 CELLS

Hope someone can help with a macro. ANYBODY !!!!!

 

[RagDyer]

You're talking in generalities.

Give some *exact* problems and/or *exact* data values that you foresee you
would not be able to *easily and quickly* revise to suit your needs.

 

[Venkatachalam.Shankaran]

Well, firstly I am a novice in Excel, but still I thought I could try.
How about, conditional formatting in a different way. Having the result
of the VLOOKUP in a cell say $C$50 & in conditional formatting, have
the values set equal to $C$50 & format

 

[Venkatachalam.Shankaran]

Well, firstly I am a novice in Excel, but still I thought I could try.
How about, conditional formatting in a different way. Having the result
of the VLOOKUP in a cell say $C$50 & in conditional formatting, have
the values set equal to $C$50 & format

 

[JAZZNAURA]

Sorry will try to be more precise,

Have a spreadsheet shows 4000 warehouse locations (location of
products). Each cell is a location in the warehouse; some products have
more than one location. I import a text file into the wookbook which
tells me which product has more than one location (2, 3, 50, etc) if a
product take up more than 1 location, the location are always next to
each other.

What I’ve done so far is to use vlookup to look at locations in file
and allocated the number (the number of locations the product has) to
the cell that correspond to that location. What I need is a macro or
formula that will look at the cells and the number within and than
highlight the cells next to it according to the number in the cell (5
in cell, highlight 5 cells, 50 highlight 50). The warehouse runs up one
aisle and then down the next and so on. So I need to be able to
highlight cells up one column and then down the next and then up, then
down and so on.

Hope this is better.

Thanks

 

[JAZZNAURA]

thanks (e-mail address removed),

i'm a novice, can you explain that more clearly.

Apologies to all for my lack of knowledge

cheers

 

[RagDyer]

You will need code with that scenario.