VBA Macro - Conditionally Pick Cells From Another Workbook?
May 23, 2012
I have 2 workbooks:
-1st workbook, I am using a worksheet (let's call it workbook1/worksheet1) already running with macro. In this worksheet, there is a column "Protocol" where different protocols are named (some are repeated, not sorted)
-2nd workbook, there is a worksheet (call it workbook2/worksheet2) are just the list of description for each protocol listed in workbook1/worksheet1. For example: width, length, height
How do I get values found in workbook2/worksheet2 and use in workbook1/worksheet1 for each corresponding protocol?
View 2 Replies
ADVERTISEMENT
Apr 27, 2013
A
B
C
D
[Code]....
There are four sheets (labeled A,B,C)
Depending on the contents in cells in Columns A and B (which have 1000+ rows), I want to change the background color of a referenced cell elsewhere:
For each row (lets say from 2-1000)
1. If "A" cell is blank -- referenced cell (address in columns C & D) should be colorless -- (as in Row 2 --- cell Z5 in Sheet "C" should be colorless)
2. If "A" cell has text AND "B" cell is blank, reference cell should be colored BLUE -- (as in Row 3 --- Cell F3 in Sheet "C" should be BLUE)
3. If both "A" and "B" cells have text, reference cell should be colored YELLOW -- (as in Row 4 --- Cell C5 in Sheet "B" should be YELLOW)
While I could do this with a conditional format in every referenced cell, it would take too long to enter them.
View 7 Replies
View Related
May 2, 2008
ABCDEFGHI need to create a macro to perform some conditional formatting1DateDayTypeTimeConfirmed Order No.Site Location2SHEET OVERVIEW301/01/08TuesdayAB4CVertically there will be 366 tables to represent 366 days and Horizontally 10 tables to represent 10 employees,5Dwhich enables the work activities of 10 employees to assigned over the period of a year.6E7AAFIn the type box marked 'A' and 'AA' the user selects from a drop down box a parameter as list below left.8GOn entry of an 'n' in the type box the user can enter data into the 'time','confirmed order','site location' boxes.9HThe idea of having 2 x type boxes is to allow the day to be split into morning and afternoon.10I1102/01/08WednesdayMACRO / VBA FUNCTIONALLITY REQUIRED 1213In the 'type' boxes if the user selects anything other than 'n' then the 4 'site location' boxes and coloured and 14the appropriate text from the list below left is inserted into the 4 associated 'site location' boxes.1516Example:1718Box marked 'A' the user selects 'h'. Boxes marked 'BCDE' and filled with colour and the text holidayBox marked 'AA' the user selects 't'. Boxes marked 'FGHI' and filled with colour and the text trainingType List Entry ParametersWhen the user selects type 'n' after the associated boxes are returned to there blank state (no colour or text)nnormal dayhholidayThe macro/vba would have to respond on everytime a 'type' box changesssickttrainingThere would be 2 x type boxes per day, 366 days a year and for 10 employees. Therefore it would have to monitoruunauthorised absence2 x 366 x 10 (7320) type boxesbbank holidayccompany shutdown
View 9 Replies
View Related
Feb 23, 2008
I need to lock a cell (F21) if J26 is less than 100. Can't work out how in conditional formatting. Is there a different way?
This is what I need to do:
F21=(IF,J26<100,Locked,Unlocked)
View 10 Replies
View Related
Dec 14, 2006
If you look in the file you will see a records sheets and a form sheet. I want to see if a record has been broken. All the times are in seconds and the distances are in centimeters. How can I find a certain row, compare and act based on an if statment?
View 2 Replies
View Related
Dec 14, 2006
Is there a way to activate a Macro with a conditional in one of the cells? It's like this, I want that if the content of A2 changes to "True" then C2,D2 and E2 change to bold and the background color change to yellow. Is there a way to do this?
View 14 Replies
View Related
Feb 5, 2009
I want the 'Model Number' to automatically be picked based on the numbers entered into Sheet1 B1 & B2. The model numbers are listed on Sheet2.
View 4 Replies
View Related
Jan 12, 2007
I have a blank log template on one sheet, On this I need to be able to enter two variables that tell other cells to pick info from the next sheet. The two variables being 1) Date and 2) DAY SHIFT or NIGHT SHIFT.
On the next sheet is an archive of data running in columns with the dates in row1 and the denoted shift (day or night) in row 2. The rest of the relevant info is in the columns below each date and shift
What formula would I use so that the log template cells fill with data from the archive when the date cell and shift cell data is entered.
View 9 Replies
View Related
Jun 4, 2014
I am attempting to create a staffing calendar that will use conditional formatting to alert the coordinator by highlighting the cell when certain thresholds are reached.
The spreadsheet has three tabs, with 'Master' being the final copy.
In the 'Magic' tab, I was able to get it work with a rolling count but I am having trouble recreating it when the count is stationary and not updating itself line by line aside the person's name.
What I'm preferably looking to have happen is for the 'Name' to light up when it appears more than 3 times in a week in the Day fields, more than 1 time a week in the O/C field and more than 7 times a month in the Night field.
View 2 Replies
View Related
Jan 10, 2012
Is it possible to conditionally a set of cells based on a different cell? Example; Background color of cells A2:A10 = blue if A1>0
View 1 Replies
View Related
Apr 18, 2012
Column A = list of names
O1:P20 MAY contain names
If A1 = Fred
AND O5, O16, P3, P19 = Fred
Selecting A1 changes all instances of "Fred" to RED-BOLD
If A2 = Felix
AND O9 = Felix
Selecting A2 changes "Felix" to RED-BOLD
Etc...
View 4 Replies
View Related
Apr 28, 2012
Is there a way to conditionally format a range of cells from VBA?
I want the cells to turn red yellow or green when i type into that cell r, y, or g.
I can easily select the range of cells, but dont know how to format them.
View 4 Replies
View Related
Jun 14, 2014
I have a problem where I need to answers from a look up table to return an empty cell if the vlookup table returns a 0 or #N/A. This has to be the case as the answers are used in the creation of a csv file.
It would be easier for me to put in conditional formatting rather rewriting 40+ formulas and copying them down 1000 rows.
Is there a method in vba to format cells to blank?
View 2 Replies
View Related
Dec 11, 2006
Lets say I have values in A1, A3 and A5 and I want to to build either a sumif or an sum if array formula to conditionally sum them. How do I do this, assuming I don't want to use VBA or named ranges, i.e. create an array of noncontiguous cells and evalute & sum them.
View 9 Replies
View Related
Apr 30, 2014
I am having trouble with the following macro,
[Code] .....
When I press F8 and step through the macro, the StartDate and EndDate both seem to populate correctly.
The macro seems to go through the motions but returns NO results, unlike the recorded macro.
Macro code to "Clear Filter".
View 2 Replies
View Related
Apr 8, 2014
I've got a dataset of certain trades across different markets and traders, and on different days. The date of the trade is in column A, the contract ID is column D and the trader ID is column J.
Traders do not trade every day. I would like to insert a new row/rows for each date on which traders do not trade between their first and last trade, for each market in which they participate.
Is this possible using macros?
And, if so, then I would like to insert the high and low prices for the market on those dates from a different worksheet into the newly created blank rows. On this second workbook the contract ID is column A, the date is column C, the high price is column F and the low price is column E. I would like the low price to be inserted into column AJ on the original worksheet and the high price into column AK.
View 2 Replies
View Related
Sep 13, 2013
I have a fairly simple macro (below). I want it to do one more thing.....not run if the filters have not been changed. I would like to prevent the error that occurs if someone tries to run it and there are no filters active.
Private Sub AutoFilter_Remove()
Sheets("Data").Select
ActiveSheet.Unprotect "password"
ActiveSheet.ShowAllData
[Code].....
View 9 Replies
View Related
May 1, 2014
I am looking to produce a Macro to select conditionally formatted cell's from a worksheet, i got as far as selecting those cell's but i need it to only select cells that have been filled.
View 6 Replies
View Related
Aug 18, 2009
I have a series of conditionally formatted cells which turn the text in them to red if the value is less than 40. This works fine.
Occasionally however a value of less than 40 will need to be entered along with the letter 'v'.
ie. 39v
I'd still like this to be coloured red, but it's obviously coming out as black.
Is there a way to sort this out?
edited to add : Im actually using a separate cell to enter the value 40 (as the value can change).
View 7 Replies
View Related
Mar 14, 2013
What I am attempting to do is have my spreadsheet automatically detect and highlight personnel working seven days in a row. There are 63 total columns, the first one being for the person's name. The next 31 columns are a full month's dates in which their day shifts are recorded, while the final 31 are the full month's dates for night shifts worked. Days worked are listed as a "1" in the cell beneath the respective shift's date, while days off go down as "3". Currently I am using conditional formatting with the following formula, which highlights cells in red:
=ISNUMBER(FIND("1111111",(H7&I7&J7&K7&L7&M7&N7&O7&P7&Q7&R7&S7&T7&U7&V7&W7&X7&Y7&Z7&AA7&AB7&AC7&AD7&AE7&AF7&AG7&AH7&AI7&AJ7&AK7&AL7)))
I created the conditional formatting formula to apply to the row for the first person listed, and then copied and pasted the formatting only for the rest of the personnel rows. The issue I'm having is that while it will highlight the seventh work days, it will often highlights all cells prior to the grouping which has seven straight days of work. It also tends to highlight single or multiple cells in red which do not fit the pattern, and whose rows may not even have seven straight days of work.
View 1 Replies
View Related
May 30, 2014
I wish to compare two sheets [Sheet1 and Sheet2] within one workbook and format the second sheet conditionally, based on an exact match to the first sheet.
Cell by cell, I can add the specific conditional formatting rule to each cell but this is tedious and its hard to see where I have applied the conditional format and where not.
So I tried to construct a macro to apply to a range of cells.
I tried to assign the logical result of the compare test to a cell outside of the range[lets say on Sheet2, A1] , and then format the range cells based on that logical value. I think my error is in conditional format criterion but I am a bit stuck.
[Code] .....
View 11 Replies
View Related
Jan 19, 2012
Any way to conditionally unlock and unprotect certain cells in a column. I have been unsuccessful with my attempts at customizing other people's vba code that is a little different than what I am trying to achieve. So here's what I would like to accomplish:
If A23 = "Please See DME", then unprotect (pw=richie13) and unlock D23, then protect (pw=richie13) sheet again
If A24 = "Please See DME", then unprotect (pw=richie13) and unlock D24, then protect (pw=richie13) sheet again
If A25 = "Please See DME", then unprotect (pw=richie13) and unlock D25, then protect (pw=richie13) sheet again
and so on and so on.
View 9 Replies
View Related
Jan 26, 2013
I'm currently working on a maintenance task sheet for a couple of generators. I have successfully created button macros to input dates. I would like a macro to copy all the cells that are RED (due to a condition rule) plus their associated fields to the left from worksheet "Schedule" to worksheet "Tasks Due". [URL]
Here's the pre-programming flow, I just don't know the code to do it:
Scan worksheet "Schedule" for red cells in following Range
H8:I16,H18:I22,H24:I35,H37:I41,H43:I49,H51:I59,H61:I72,H74:I75,H77:I84,H86:I89,H91:H100
If cell = red Then Copy Row A:I
Insert Row into sheet ("Tasks Due")
If red cell is in worksheet "Schedule" H8:I16 then insert copied cells in worksheet "Tasks Due" one below cell = Fuel System
If red cell is in worksheet "Schedule" H18:I22 then insert copied cells in worksheet "Tasks Due" one below cell = Lubrication System
[Code] .....
If my uploaded workbook doesn't work I've linked screenshots of my document.
Top of "Schedule" worksheet: [URL] .....
Middle of "Schedule" worksheet: [URL] .....
Bottom of "Schedule" worksheet: [URL] .....
"Tasks Due" worksheet: [URL] .....
View 4 Replies
View Related
Dec 11, 2012
I am attempting to count a number of cells in one column so long as they fall within a certain date, which is located in another cell in the same column. I'm trying to account for a number of reports that happened within a certain week.
The formula I came up with was
=COUNTA(COUNTIFS(A_DATE,">="&B2,A_DATE,"<="&D2))
But this returns 1 more then it should no matter what. Even when all cells within A_DATE are empty. it returns 1.
I know the COUNTIFS portion works to count the cells between certain dates.
Is what i'm looking to do possible or am I suffering from Excel burnout and just can't see that logicaly it makes no sense?
View 2 Replies
View Related
Jan 18, 2013
I had a list of people on sheet1 of my workbook, and on sheet 2 a table with a list of names and their department.
I started off by wanting to use VLOOKUP within the conditional formatting feature of Excel to find anyone who worked in "finance" in sheet 2. I could get this formula to work on the sheet but as soon as I put it into the conditional format feature it wouldn't work.
Trying to find a work around I added a column in sheet 1 where I inputted the VLOOKUP formula and caused it to display "1" if the person worked in finance. It looked like this:
A: Person's name
B: Value of 1 or 0
The idea was to hide column B and use conditional formatting to say "if column B2 = 1 colour column A2 blue". Now I could get this to work on a single row but couldn't make it work over a range, ie. "if cells A2:A150 have a 1 in the column next to them colour the relevent cell blue"
I feel I'm missing the obvious. I found quite a few answers online but non actually specified how to get the conditional formatting to recognise the relevent cell it should be looking at in a range.
1) How would I get conditional formatting working over a range
2) Whether it is possible to use VLOOKUP within the conditional formatting function of Excel.
View 3 Replies
View Related
Aug 5, 2014
spread sheet that I need to do for work. Unfortunately I do not have excel on my home computer to be able to attach a spread sheet
The spreadsheet has one column (A) with a list of questions. The column next to this (B) has either a red or yellow cell in each row (a red cell would be a high risk to the business if the answer to the question in that row was no, and a yellow cell indicates a moderate risk to the business in the answer to the question in the row was no).
The third column (C) is conditionally formatted so if a 'y' was placed in any of the cells they would turn green. If 'n' is placed in any of the cells the cell would change to either red or yellow (this would depend on what the colour was in column B).
I need to know a formula to count cells by colour. So the number of red, yellow and green cells in column C would be counted automatically into a totals box for each colour at the bottom of the spreadsheet. I have tried some online suggestions but couldn't get these to work for cells that had been conditionally formatted. I'm not the most experienced person with spreadsheets (this time last week I couldn't add two cells together)
The final thing I need from the spreadsheet (and I'm not even sure if this is possible) is for a total box to be colour co-ordinated based on the number of red, yellow and green cells in column C. I would need the total box to be green if all column c is green, yellow if three or less cells in column C are yellow and red if any of the cells in column C are red or there are more than 3 yellow cells in column C.
View 2 Replies
View Related
Jun 25, 2008
When using a Pivot Table I regularly filter on known data specific to me i.e. WBS codes.
Column ‘A’ contains the WBS Codes which can be as many a 1000, however, I only need to filter out the 10 or so I require, and currently I uncheck all the records and manually scroll through the list to check the ones I require.
In order to speed things up I recorded a macro which works fine, however, If I need to check any new codes I would have to record the macro again, as I tried to edit the Macro and all it does is to un check all the other codes I don't need.
Is it possible to create a Macro that picks up a range of data that is then used to filter on. (i.e. only the records I require)
View 9 Replies
View Related
Jul 29, 2014
I have the same report, one a monday snapshot, the other a daily comparison. I would like to index columns B, in both reports, to find the same part number and then reference the horizontal row and highlight increases in columns G thru AD. The comparison report looks identical but the column B numbers may not be in the same location. this is a report to show prosduction requirements and the increases throughout the week.
View 1 Replies
View Related
Jan 12, 2009
I have created a macro to open a workbook from another workbook and do a vlookup against the data in the source workbook. I then wanted to highlight the cells in the in the workbook that I'm doing the vlookup from, and to unhighlight any empty cells. I did this all without code using the macro recorder so that I could then study the code and learn something.
View 2 Replies
View Related
Oct 31, 2006
I have a worksheet template with details in cell A1,B1,C1,D1,E1,F1,G1,H1 & I1and when the sheet closes I would like to save a copy of the cell contents to a different workbook called transaction history, is there any way that vba code could write this to the workbook and when the transaction happens again, it then writes the new transaction data to the next row within the same workbook transaction history
View 6 Replies
View Related
