Excel conditional formating

[Guest]

I created a spread sheet for a shopping list for my wife cell column "A"
contains name of product-column "B" contains on hand number with conditional
formating that if there is less than 2 on hand then the number will turn red
and bolded. column "C" show the item price and column "D" shows the store
where to purchase. This spreadsheet works great.. what I would like to do now
is, copy the items that have met the condition and have turned red and bolded
from column "b" be linked to another spreadsheet with in the same workbook
along with the information from from column a,c and d only

 

[Guest]

James,

Assuming you were looking for a vba solution, try this...

'add a command button to the sheet with the inventory
'and paste this code into the click event for that button

'assumes procedure is driven from active sheet containing the inventory
'assumes shopping list is on sheet 2
'assumes no empty spaces in item list, i.e., if cell A2 has an item, A3 is
blank
'and A4 has an item, the do loop will terminate when A3 is found empty

Dim x As Integer, y As Integer
x = 2 'allow for 1 header row on inventory sheet
y = 2 'allow for 1 header row on shopping list sheet
Do While Cells(x, 1).Value <> ""
If Cells(x, 2).Value < 2 Then
Sheets(2).Cells(y, 1).Value = Cells(x, 1).Value
Sheets(2).Cells(y, 2).Value = Cells(x, 3).Value
Sheets(2).Cells(y, 3).Value = Cells(x, 4).Value
y = y + 1
End If
x = x + 1
Loop



Another thing you could consider is variable thresholds, e.g., a helper
column contains the minimum qty for each item, if it isn't desirable to
always be 1. If its always 1, what you have works fine. The CF looks at the
helper column to decide whether or not to light up. You use a relative row
reference in the CF formula so you can fill the other rows with the CF.

Roy