|
Possible to highlight cells depending on value from vlook up?
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 -- JAZZNAURA ------------------------------------------------------------------------ JAZZNAURA's Profile: http://www.excelforum.com/member.php...o&userid=36961 View this thread: http://www.excelforum.com/showthread...hreadid=572793 |
Possible to highlight cells depending on value from vlook up?
This would require programming in VBA.
Doable, but not with a standard Excel formula. "JAZZNAURA" wrote: 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 dont know if this is straight forward or not. Hope Ive explained myself clearly, any assistance would be appreciated. Cheers -- JAZZNAURA ------------------------------------------------------------------------ JAZZNAURA's Profile: http://www.excelforum.com/member.php...o&userid=36961 View this thread: http://www.excelforum.com/showthread...hreadid=572793 |
Possible to highlight cells depending on value from vlook up?
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. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "JAZZNAURA" wrote in message ... 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 -- JAZZNAURA ------------------------------------------------------------------------ JAZZNAURA's Profile: http://www.excelforum.com/member.php...o&userid=36961 View this thread: http://www.excelforum.com/showthread...hreadid=572793 |
Possible to highlight cells depending on value from vlook up?
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 -- JAZZNAURA ------------------------------------------------------------------------ JAZZNAURA's Profile: http://www.excelforum.com/member.php...o&userid=36961 View this thread: http://www.excelforum.com/showthread...hreadid=572793 |
Possible to highlight cells depending on value from vlook up?
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" wrote: 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 didnt 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 Im correct in my thinking here. Apologies if Ive 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 -- JAZZNAURA ------------------------------------------------------------------------ JAZZNAURA's Profile: http://www.excelforum.com/member.php...o&userid=36961 View this thread: http://www.excelforum.com/showthread...hreadid=572793 |
Possible to highlight cells depending on value from vlook up?
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 !!!!! ;) -- JAZZNAURA ------------------------------------------------------------------------ JAZZNAURA's Profile: http://www.excelforum.com/member.php...o&userid=36961 View this thread: http://www.excelforum.com/showthread...hreadid=572793 |
Possible to highlight cells depending on value from vlook up?
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. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "JAZZNAURA" wrote in message ... 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 !!!!! ;) -- JAZZNAURA ------------------------------------------------------------------------ JAZZNAURA's Profile: http://www.excelforum.com/member.php...o&userid=36961 View this thread: http://www.excelforum.com/showthread...hreadid=572793 |
Possible to highlight cells depending on value from vlook up?
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 RagDyer wrote: 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. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "JAZZNAURA" wrote in message ... 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 !!!!! ;) -- JAZZNAURA ------------------------------------------------------------------------ JAZZNAURA's Profile: http://www.excelforum.com/member.php...o&userid=36961 View this thread: http://www.excelforum.com/showthread...hreadid=572793 |
Possible to highlight cells depending on value from vlook up?
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 RagDyer wrote: 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. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "JAZZNAURA" wrote in message ... 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 !!!!! ;) -- JAZZNAURA ------------------------------------------------------------------------ JAZZNAURA's Profile: http://www.excelforum.com/member.php...o&userid=36961 View this thread: http://www.excelforum.com/showthread...hreadid=572793 |
Possible to highlight cells depending on value from vlook up?
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 ------------------------------------------------------------------------ JAZZNAURA's Profile: http://www.excelforum.com/member.php...o&userid=36961 View this thread: http://www.excelforum.com/showthread...hreadid=572793 |
Possible to highlight cells depending on value from vlook up?
thanks , i'm a novice, can you explain that more clearly. Apologies to all for my lack of knowledge cheers -- JAZZNAURA ------------------------------------------------------------------------ JAZZNAURA's Profile: http://www.excelforum.com/member.php...o&userid=36961 View this thread: http://www.excelforum.com/showthread...hreadid=572793 |
Possible to highlight cells depending on value from vlook up?
You will need code with that scenario.
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "JAZZNAURA" wrote in message ... 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 ------------------------------------------------------------------------ JAZZNAURA's Profile: http://www.excelforum.com/member.php...o&userid=36961 View this thread: http://www.excelforum.com/showthread...hreadid=572793 |
| All times are GMT +1. The time now is 03:27 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com