Highlighting A Row Based On Cell Value
Column A is all random dates . When the date happens on the 1st I need to highlight that row. I'm pretty sure conditional formatting is the way I need to go but have had no luck. The date format is dd/mm/yy.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Highlighting Active Cell's Row, Along With Any Row That Shares Same Value In That Column
Is it possible to click on a cell in column C, and have the wishlist below happen: That active cell's row is hightlighted. Any cell in that column that has the same value as active cell is also highlighted. Plus, any cell in another sheet that has that value it's row is highlighted too. Example: I click on C5 in Sheet 2 its value is 45000789 it row is highlighted, this value also appears in C3 in the same sheet, so it's row is highlighted as well. Plus, in sheet 1 in C10 this value appears and it's row is highlighted as well. When any of the values are clicked again the highlight is removed from all parties.
View Replies!
View Related
Highlighting The Row With The Selected Cell
I work with many sheets that have many data columns and have used a "greenbar" formula in conditional formating but would like to have a similar formula that highlights the current row. If possible, it would be nice if this new formula would not wipe out all other formatting as it moves. I have seen Ivan's work at www.mrexcel.com/hof001.php but it says that it wipes out other formatting. The formula that I am currently using is =MOD(ROW(),3)=2 Is there a way of getting the current row number so that it could be =ROW()=(current row number) ?
View Replies!
View Related
Highlighting A Cell, Based On Value / Blank.
I have a macro, and it mainly works, except the second part where it checks if the value is less than 7, and returns it to a white background. The cell has a formula in it that returns "" based on another series of cells. I have considered changing that to a zero and formating, but for reasons that don't need explanation, i have chosen not to. here is the macro. I need the second part to be activated if less than 7, as well as if the cell returns ""
View Replies!
View Related
Select An Item Of Data ( Or Update A Particular Cell ) Then I Loose The Highlighting For That Row
I have a big spreadsheet that has 30 columns. The data is downloaded from an SQL database from time to time. The row identifying data ( the record number/key ) is in the left most cell. I often have to move over 10 to 20 columns to view some other data in the same row. I can do this by selecting the preset Excel row number which higlights the whole row and then use the lower slider bar to go to the correct column to view the data. However if I wish to select an item of data ( or update a particular cell ) then I loose the highlighting for that row and I can "loose" which row I am on. ( maybe I'm stupid but it does happen ) Can I use Conditional formatting to highlight a row whenever I alight on any data in that row and it sttays highlighted until I select another value on another row?
View Replies!
View Related
Highlighting Entire Row
This problem has been driving me insane, as I thought it wasn't possible to do in excel however this morning I accidentally pressed a shortcut key on my keyboard and what I wanted, happened.. pity I can't remember how I did it. How do I highlight an entire row, but remains highlighted when i enter info in the cells until i move down to the next cell? Eg. I click on row"1" so that all cells are highlighted, then while entering info in B:1 - the entire row is still highlighted? This will be very useful in my line of work. I know there was some information on conditioning formatting, but surely there is a way to do it via keyboard shortcut since i accidentally did it just before.
View Replies!
View Related
Highlighting A Row And Having That Data Tranfered To Another Worksheet
I have 1 Excel Doc that has 3 worksheets (DATA, COVER, TPA). DATA is where i input the data and COVER and TPA are where the data needs to end up. both COVER and TPA are standardized sheets that i do not change...only the data coming in will change. How can i make it so that whatever row in DATA i highlight, that row's data will be entered in to the correct spots on both COVER and TPA. when i highlight a different row, then the data will change to that row's data in both COVER and TPA (hope i'm explaining this correctly). right now i can only do 1 row by using (=DATA!$D$2). basically i want the '2' that is representing the row to be whatever row i have highlighted. if i highlight row 51 i want the formula to read (=DATA!$D$51). can anyone please help. i can put up a copy of the spreadsheet if need be, i am just hesitant cause of the confidential info. i would need to change the names and numbers to protect the innocent.
View Replies!
View Related
Adding Data By Row Based On Typing In Cell Contained In Row
I've built (but not completed) a spreadsheet used to organize multiple Bill of Material lists. This list is a row-by row list of products with information relating to a specific part number including: Mfg, Part#, Qty Used, Cost Ea, Vendor, among many more. Here's what I'm trying to accomplish first. As this list grows, my intension is the be able to recall a row of data (or control what columns of the row are recalled) from this list and be able to easily add them to the list. I would like to be able to do this by typing in data in one cell in the row (like the Part#), and then have a macro or VB code that will automatically fill in a defined number of columns with the descriptive data like: Mfg, Cost Ea, Vendor, etc. Currently, the data for the rows (if it already exists) would reside higher up in the list on the same worksheet but could also potentially be on another tab in the workbook.
View Replies!
View Related
Format Row Based On Single Cell Value In That Row
I have a macro that's supposed to see if cell 5 meets criteria and continue to all sheets in workbook, except " Total". When I activate the macro on a specific page, it runs smoothly on that page. But when it's finished, it doesn't continue to the next sheet in the workbook. Sub ColourBG() Dim ws As Worksheet Dim line As Integer endline = Range("A1000").End(xlUp).Row For Each ws In Worksheets If ws.Name <> "Total" Then With ws For line = 3 To endline Application. ScreenUpdating = False If (Cells(line, 5).Value = "0206") Then _ Cells(line, 1).EntireRow.Font.ColorIndex = 5 '*(Blue) Next line Application.ScreenUpdating = True End With End If Next ws End Sub
View Replies!
View Related
Color Row Range Based On Cell On Same Row
I have an excel sheet in which i have a cell A11 with drop down list values=YES/NO. Now based on the value in this cell i want to fill color in the cells(B1 to B10) i.e for e.g. if i select YES in the cell A11 then the cells(B1 TO B10) should become green in color. Attached is an sample of what i want.
View Replies!
View Related
Highlighting The Active Cell After A Highlight
I have a cover sheet to a workbook that holds hyperlinks to its relevent page. Several of the hyperlinks link to the same page but different relevant cells. However it is not overly clear which cell is active when you click the hyperlink. What I would like is for the active cell to be highlighted on the page. EG hyperlink 1 links to page 1 cell A2 & hyperlink 2 links to page 1 cell B6 - I would like A2 to be highlighted when accessed by hyperlink 1 & B6 highlighted when accessed by hyperlink 2.
View Replies!
View Related
Highlighting Cells Depending On A Value In Another Cell
Have a spreadsheet shows 4000 warehouse locations (location of products). Each cell is a location in the warehouse (ba050, ca809, etc, actual warehouse locations, not grid ref); some products have more than one location, the locations are always next to each other. I import a text file into the workbook which tells me which product has more than one location (2, 10, 50, etc), that’s all I’m interested in ( multiple locations). What I’ve done so far is to use vlookup to look at locations in file and allocated the number (the number of locations that product has) next (next column) to the cell that correspond to that location. What I need is a macro or formula that will look at the number and than highlight that many cells up or down the column, according to the number in the cell to the right (5 in cell, highlight 5 cells, 50 highlights 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. Multiple locations change on a daily bases, so need a quick way of updating 4000 locations. New to excel don’t know if this is possible or not. Learning as I go. Need some assistance. Sample below. Only interested if a number is returned, “******” can be ignored as will hide all columns expect ones with locations. So: BA020 returned value of 4, so would like to highlight (any colour) BA020, BA029, BA040, BA049. BB780 returned value of 2, so would like to highlight BB780, BB789. POSSIBLE? BA090 2 BA100 **** BB749 **** BB739 **** BA079 **** BA089 **** BB760 **** BB750 **** BA070 **** BA080 **** BB769 **** BB759 **** BA059 **** BA069 **** BB780 2 BB770 **** BA050 **** BA060 4 BB789 **** BB779 **** BA039 **** BA049 **** BB800 **** BB790 **** BA030 4 BA040 **** BB809 **** BB799 **** BA019 **** BA029 **** BB820 **** BB810 **** BA010 **** BA020 4 BB829 **** BB819 ****
View Replies!
View Related
Lookup Data In Row & Cell Based On Data In Row And Cell
I am looking for a way to do what i would call a dual vlookup. i have some data in a number of sheets and need to do some consolidation into a summary sheet. problem is, not all the sheets have data in the same columns and rows but all the data is the same. what i am trying to do is something like vlookup("a",sheet2!A:M,***need to put the column of the data here***,0) i cant just put in the column number because it changes on each sheet, need some way to do another lookup there, like an hlookup or something, so that i could lookup which column contained 'sales' and return that number so it can be put into the vlookup.
View Replies!
View Related
Highlighting Cells Based On Cells Values In VBA
I need to highlight a cell when its value exceeds parameters based on the production line it comes from. If the line is K11, then i need this cell to highlight when its value is either < 0 or greater than 221. If the line is K21, than it needs to highlight when its value is <0 or greater than 474.
View Replies!
View Related
Give Cell A Value Based On Length Of Row (variable Row Length)
My worksheet contains data with the reaction times on a psychological test. Each respondent in the test has 280 rows in my excel sheet. The 'perfect' length of the row, is from A to M. When an error is made in the test, the length of the row will increase. So the error length can be A to AA. For me it is important to analyse the error. So I would like to give a perfect row length, the value 1, and an error row length a value 2. So, in conclusion: If: Cell length = A1 - M1? --> Copy A1 B1 C1 (A B Cof that row) to Sheet3, and give D1 in sheet 3 the value 1 Cell length >= A1 - M1? --> Copy A1 B1 C1 (A B C of that row) to Sheet3, and give D1 in sheet 3 the value 2
View Replies!
View Related
Identify Row Number Based On Value In A Cell And Use That Row Number In A Macro
I have Sheet with 40 employees who each proposes their work schedule, so I have to give each Employee access to the same sheet and want highlight and unlock only those cells that specific employee can use. Each employee has to login from a drop-down (sourced from Sheet.Employee Master), so their unique Employee Number is in "A13" of Sheet.LOGIN Can I identify the ROW number and then use that ROW number in a macro to highlight and unlock specific Range of Cells in Sheet.PROPOSED SCHEDULE? ---where "Sheet.LOGIN("A13") = (the value in the cell Col A:"row" of Sheet.PROPOSED SCHEDULE) I have attached a scaled down version of the Workbook. Following code is scaled down-- this is for Employee 02 who appears on ROW 16 of the sheet. (macro is same for each employee, just uses a different row)
View Replies!
View Related
Copy Row Based On A Cell Value
I have a spreadsheet that contains office names (Brookside, Blairsville, New Castle, etc) in Column A. The rows next to those show student names and test scores, etc. I'd like a formula to take each office and copy the data belonging to that office and paste it to a sheet for that office. For example: A B C Brookside Tom 82 Brookside Bill 90 New Castle Phil 80 I'd like to take Brookside's data and past it to a 'Brookside' sheet.
View Replies!
View Related
Way To Hide Row Based On The Value Of A Particular Cell
I have a worksheet with an age row of 20 - 99 (and a bunch of other data) A user enters the starting age, and then the sheet fills in the rest of the cells. If the user enters 50, the age fills from 50 to 130. Before printing, I manually hide the rows (using the hide row command) over age 99. Is there an easy way to do this without macros? I am Excel impaired! I tried using Auto filter - but I have to rerun the filter each time (it is not automatic).
View Replies!
View Related
Hide Row Based On Cell Value ..
I would like a macro where I it will examine Cells A8 down to A27 to see if there is a value. If there isn't a value, I want that row hidden. I would like for it to always run (i.e. if a value is put into that cell, the row reappears, otherwise it is always hidden).
View Replies!
View Related
Highlight A Row Based On A Cell
I have a macro that already works well but I need to make a change to it and I can't figure out how to do it. I need so when the macro is run it will prompt the user for a date. After they enter the date it will highlight cells A:K on each row that data appers. A little background for this. It is a schedule for a call centre which can show two to three weeks of schedules per agent. We rerun the macro each day to update break times and I would like it so it will highlight todays line.
View Replies!
View Related
Replace One Row With Another Based On A Cell
I have a table that i would like to update. All my macro needs to do is search the entire first column of a table for a match to cell A1, there will only be 1 match, once the match is found row b's values need to replace the row thats entry in its first column matched cell A1.
View Replies!
View Related
Row Count Based On Cell Value
I have a table that is say 5 colums wide. The top row in the table are just labels (Number, Height, Width, Density, Mass for example) The rows under that row would be filled with manually inputed data (except for "Number", this would automatically fill down) The last row of this table, however long it may be (however many datapoints) has data that sums and averages the data above it (average height, width, density, etc) So if I have say 10 samples, I need a total of 12 rows, 1 for the header and 1 for the footer and ten for the samples. What I would like, is to have a cell that I input the number of samples into (say there are 53 samples, Cell A1 would say "53) and the table with the data would automatically adjust (insert or delete rows, with the cumulitive data adjusting appropriately)
View Replies!
View Related
Get A Cell To Look At A Row Based On Contents
I want to have a cell that looks at another cell based on a number that is in another cell. So imagine if you will: A1 contains the number 25. A2 contains the formula I can't work out. I want it to look at the row that is the number contained in cell A1, so in this case it would look at row 25. Now I want to narrow it down further to a cell in that row but this will be a constant so lets say column F. So if A1=25 then A2 would display what ever is in F25. If cell A1 were 16 it would display the contents of F16 etc. The best I got was something like =IF(A1=>1,(F(A1))) but obviously that is wrong as it doesn't work.
View Replies!
View Related
Delete Row Based On Cell Below
I currently have a spreadsheet with circa 50,000 lines with info regarding our websites at work. In every row, column "A" contains a unique reference number.There eill either be 1 row for this reference number, or there will be 2 rows. there will never be more than 2 rows for each reference. I wanted some code to say that if theres only one row, then delete it but if the if the cell underneath it is the same then its ok. I tried the following but it deletes the row anyway, supposing the cell under it is the same or not Sub test() Dim icell icell = ActiveCell.Offset(1, 0).Value Range("A1").Select Do If ActiveCell.Value = icell Then ActiveCell.Offset(2, 0).Select Else ActiveCell.Rows.Delete End If Loop Until ActiveCell.Value = "" End Sub
View Replies!
View Related
Lock Row Based On Cell Contents
I wish to be able to lock an entire row after an entry has been made in a certain cell. e.g. let's say i want to lock row 9 in its' entirety following entry of "Yes" in cell G9. If there is no entry in G9 the the entire row should be still unlocked. Likewise with any row with a corresponding entry in column G.
View Replies!
View Related
Insert Next Row Based On Previous Cell Value
My current code works, but there's got to be a shorter version to insert rows based on a cell value. Currently my code works on a series of If statements. If the value in the current cell is "2" then goto the next row and insert one line. If the value is "3" then go to the next row, insert, next row insert etc. I'm currently written up to a value of 10, but the coding is getting longer and longer. Anybody got a shorter loop that I could use.
View Replies!
View Related
Sum Up A Row Of Numbers Based On Another Cell
I have been finding answers on this website for soo long. But today is the day, I finally am stumped and could not find an answer here. I am setting up a standard excel sheet of data. Row 1 has labels and the data goes down each column. the first column is 1 of only 5 words. the rest of the columns are number values. Example. city num1 num2 num3 cityname 3 4 5 cityname1 2 3 4 cityname 3 1 2 The data in the first column will be 1 of 5 city names. My question is how can I setup a cell to keep a sum of num1, num2 and num3 of each of the 5 cities. I have been scratching my head and googleing for a couple hours now. I was thinking of using an if then statement. Here is my psuedo statement for a cell that would be keeping track of num1: (if any cell in column 'city' = cityname add the current value in this cell to the value located in column num1 of the row thats currently being evaluated. The cell that contains this formula would have to constantly monitor the city column and update the num1 column as needed.
View Replies!
View Related
Highlight Entire Row Based On Cell Value
From what I understand this is a simple process but since I am new to programming and I am in need of help. I am trying to write into one of my macros that as it is searching in the sheet if it finds CRL in column C and if it finds a value between -1 and 100,000 in column K, then it will highlight the entire row green.What can I do to add this into my macro?
View Replies!
View Related
Find Cell Based On Column And Row
I have a spreadsheet which has about 100 different SKUs in column A and the inventory for each SKU by period in columns B:Z So for example B would be P1W1 and C would be P2W2, etc what kind of lookup function would I use in VBA to find per say, the value of SKU #: "27017" in P1W2 ? Apart from pivot table.
View Replies!
View Related
Hide Row Based On Other Cell Value (Filter)
i have been able to get most of my VB work on my own (i'm learning). Currently I'm having a bit of difficulty in hiding a row based on a cell value, potentially due to cells being merged. I'd like to select from a dropdown cell to filter on a row's value. Attached you will find a sample sheet that works named "Working" in it's current state and the one "TEST" that does not filter every other line item.
View Replies!
View Related
Finding Row Number Of A Cell Based On A Particular Value
I need to find the row number of a cell based on a particular value. I am populating a row within a spreadsheet with a value (the columns have unique identifiers). After that is done, I need to go to a different spreadsheet, grab different values, and place them in a different column in the above referenced spreadsheet. So, what I want to do is find the row number for the unique identifier, then place the value in the column.
View Replies!
View Related
Change Row Based On Cell Update
I`m trying to work out the best way to propogate a cell's format accross an entire range. What I ultimately want is for a user to only have to change the text colour of one cell, and for the entire row to immediately change to the same colour with it. Even if this means always having to update for example the cell in column A to change the text colour accross that row.
View Replies!
View Related
Select Row Based On Cell Criteria
I am trying to create a macro that selects an entire row based on a date criteria. The explanation is as follows: I have fields in an excel sheet such as: Name, Date, Place, Time, Country etc….. There are many rows with data in them…. I require a prompt box that asks for a date range and then selects all the contents matching that criteria and copies into another excel sheet called Export.xls.
View Replies!
View Related
How To Hide/undide A Row, Based On A Cell Value (Macro)
I have the data as follows: columns B,c,d,e,f,g,h contains data and column A is empty. What i want is 1. Initially columns c,d,e,f,g,h should be hidden. Only a & b should be visible 2. if i enter a 0 (zero) in A1, then, columns B,c,d,e,f,g,h should remain hidden, for row-1. 3. if i enter a 1 (one) in A1, then, columns B,c,d,e,f,g,h should be visible, for row-1.
View Replies!
View Related
Delete A Complete Row Based On Time Value In Cell
I have a spreadsheet with date and time values of the format "dd/mm/yyyy hh:mm" in column A followed by some other data in cells of that row. What I'd like to do is have a macro that will delete a complete row if the time value in column A lies between two times that I can specify in the macro (the dates are irrelvant)
View Replies!
View Related
Formatting Row Of Cells Based On A Cell Content
I want to format a range of cells based on the name listed in a cell in column A (starting @A6). I currently do this manually. It is to help me visually see the line I am working with and for what person. I am almost certain that is going to take a VBA, but I know little to nothing about setting one up. I have attached a sample. I am using Excel 2003.
View Replies!
View Related
Color Code Cells Based On Value In Row 1 And Value In Cell
I have 16 columns (B:Q) Row 1 contains either a number, the letter x, or the letter a. Cells in Range("B2:Q628) contains either the letter x or is blank If a cell in row 1 contains an x, then all cells in that column with an x has an Interior.ColorIndex of 45 If a cell in row 1 contains an a, then all cells in that column with a blank has an Interior.ColorIndex of 45 If a cell in row 1 contains has a number, then nothing
View Replies!
View Related
Auto Sort- Row Refernce Based On Cell
I am typing a number into Cell B4 (ex. zza4582) this number is a serial number that will be in many differnt cells in column "A" on a different sheet "Template Paste". On each row with that serial number I need the information from "A-I" to be copied in the main sheet starting on row 10 and continuing on from row to row depending on how many times the serial number is found.
View Replies!
View Related
|
TRACKER
