Highlighting Column And Row Intersection Cells
Any code or formula to highlight the intersected cells between a column and a row for any selected cell ?
Ex: if i select E25 the cells of column E1:E25 as well as the cells of Row A25:E25 to be highlighted in a certain color , and so on for any selected cell..
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Select Intersection Of Column And Row
First although it returns the correct column, it always returns Row 1. Beyond me! Secondly I could really use some help in a line or two of code to select the cell at the intersection of the column and row. Sub test() Dim name As String Dim number As String Dim col As Integer Dim row As Integer Dim RowNumber As Integer Dim ColNumber As Integer Dim rng As Range ActiveWorkbook.Sheets("Sheet1").Activate name = InputBox("type a name from Column A")...........
View Replies!
View Related
Row & Column Intersection Value
how to write a macro that reads the point where a cell and collumn intersect? Below is my example: 1 6.25 3 9 7.25 10960700 10711700 10481100 10267000 7.5 10756600 10513300 10288000 10078800 7.75 10557200 10319500 10099400 9895000 8 10362400 10130100 9915100 9715400 8.25 10172200 9945200 9735100 9539900 How do I write a macro to find the intersection of 7.75 and 3, which is 10099400?
View Replies!
View Related
Cell/Range Names Based On Row & Column Intersection
i have a table in excel with 36 rows and 36 columns i.e. 36x36=1296 individual cells. Columns and rows have headers/labels i.e. 36 rows with row label headings and similarly 36 columns with column label headings. I would like to automatically create unique cell names for the 1296 cells based on where the columns and rows intersect using the row and column labels. The cell name format I would like to create is [ColumnLabel_RowLabel]. I've manually created the cell names in the attached file as an example of what i would am seeking (i've greyed the cells that I've done this for).
View Replies!
View Related
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 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 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 Certain Value In A Column
i am trying to get a VBA to find the value 'TRCO' select the row and highlight the row in red, i have tried the way below but i get an error saying 'type mismatch'. If Range("b7", "b51166").Value = "TRCO" Then Rows.Select Selection.Font.ColorIndex = 3 End If
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
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
Highlighting A Range Of Cells Dependant On Data In Any Of Those Cells
On the attached sheet I am trying to indicate that enough data has been entered by highlighting the entire column. Any 3 cells in Rows 7-13 and 18-36 will be filled in with any of the values from cells O6-O11 (hidden). When the total = 6 i would like to highlight the entire column to indicate it has been completed. The aim is two fold: to ensure that the correct number of points is allocated in each race, and secondly to indicate which column is the next to be filled in (as human error sometimes misses the column and adds the points to either the previous or next columns). You could call it idiot proofing the sheet. At present i have the cells in Row 37 conditional formatted to show this, but would much prefer the entire column to highlight.
View Replies!
View Related
Highlighting The Odd Value In A Column
I have a worksheet, the data flows from different columns, like a,b,c and the result is in d column. The entries are spread from 1 to 45 rows. The format of data is five digit number. As I have formatted the cells as "custom" like "00000", the Zero is shown on left side, but when the column is copied/pasted in another column in certain entries zero disappears. Is there any way to get the 4 digit entries, highlighted, so Zero can be added on left side manually directly to the concerned entries, instead of checking each and every entry.
View Replies!
View Related
Highlighting Saturdays And Sundays In A Column
I am running Excel for Mac V2008. My spreadsheet has various columns with column A representing dates of a 6 month period formatted as: ddd d-mmm-yy, one day per cell. I want to highlight the cells which are Saturdays and Sundays and are not working days for my Plant. I think it may be done using conditional formatting, but I have been unable to discover the right formula which would allow this to happen.
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 Specific Cells
this seems like such a simple thing to do but I just cannot suus it out. Basically I want to Highlight a cell depending on another cells value: ie. I have a value in Cell A1 and a value in Cell B1, in Cell C1 I have an IF statement that dsplays the word 'NO' if the values in A1 & B1 are not equal. In Cell D1 I have the word Fault. I would like to highlight Cell D1 if the Cell C1 displays the word 'NO'
View Replies!
View Related
Auto Highlighting Cells
Is there a way to automaticaly highlight multiple cells if there is data in two other cells else were? What kind of programming will I have to use. I.E. highlight cells D1:E15 only if there is data in cells A:2 & B2
View Replies!
View Related
Highlighting ARRAY Formula Cells
Essentially I have inherited a spreadsheet which is crammed full of complex array formulas. No problem in itself as I understand array formulas pretty well (or so I think!). My problem is that when i want to ammend some of these, i cannot easily see what range I need to highlight in order to edit them i.e. I obviously cant do a single cell as its part of an array. I hope I have made myself clear? So my question is this. When the cursor is sat in a cell that is part of an array formula, how can I easily find out ( ideally by the array formula range being highlighted in (say) red ) what cells are included in the array formula? Would conditional formating do the job?
View Replies!
View Related
Highlighting If A Range Of Cells Meet A Value
I have a worksheet 200+ rows with 7 columns (euro lottery numbers) I had great help last night to highlight any matching numbers. I'm getting lazy now, can anyone help with a formula to highlight a cell in an adjacent column when 2 or more numbers in the corresponding cells match the winning numbers, I know all I have to do is look down the columns and check but if a simple formula could be inserted it would save me perhaps missing something.
View Replies!
View Related
Highlighting Or Deleting Specific Cells
I have Column A with 380 cells filled in with numbers (A1-A380) i.e. numbers like..456758, 567848 etc etc Column B has a subset of those numbers, maybe 80-90 or so (B1-B90). What I want to do is either highlight the numbers in Column A that also appear in Column B in yellow, OR delete them from Column A altogether
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
Highlighting Mulitple Cells With A Macro
I need to highlight cells for all of the products and rates I need to get pricing for. I have a macro that goes out to my data and pulls in each different rate and program (columns BM:BN). That macro also sets the first note rate in the range to the lowest of the group, so this will always be different. I now manually highlight each cell that I need to get pricing, but would like to create a macro that looks up those codes and rates in column BM:BN and highlights the cells for me. I need it to highlight in all 7 boxes. I've attached a sample worksheet of what it looks like after I highlight the cells.
View Replies!
View Related
Highlighting Range Of Cells In VBA
What is the line of code in VBA that allows me to highlight a range of cells (in a column) to the end of the range? e.g. Selection is A2, highlight to end of range in column A. I know this is simple, but I'm new to VBA. Unsuccessful searching the posts.
View Replies!
View Related
Copy Non Blank Cells In Column Up 1 Row To Another Column
I need a macro to copy and paste data from Col A to Col B. But I need it pasted 1 row up. In other words if A6 has data I need it pasted in B5 and Bolded. I got a start but don't know how to finish. Dim ii As Long For ii = lastrow To 6 Step -1 If Not IsEmpty(. Range("A" & ii).Value) Then ****.Range("A" & ii).Copy***** Next ii
View Replies!
View Related
Highlighting Cells That Have Duplication In A Set Number Of Charictors
each month i have to clens a sheet that shows customer name and then the figures per month. this is pulled from a system that allows for a new entry each time a variation on customer name is used, I.E Joe Blogs LTD Joe Blogs Limited Joe Blogs ltd. would all come through as new entries. then i have to manually consolidate the figures from each month. the spreadsheet has over 5000 rows, and maybe only 15% are duplicated. what i want is to be able to select the column (that will be sorted a-z) and highlight cells that have a match for the first 4 or 5 charicters. once i have identified duplicates i then have to merge the data into one row. is there an easy way to do this? it is monthly, comparing 2008 and 2009, so by december there will be 24 columns + the company name that need to be clensed.
View Replies!
View Related
Moving Cells From A Row To A Column
it is possible to move a row of cells with (names in each cell) to a column. Basically, I want the list of names to go from top to bottom rather than left to right. From this (each name is in separate cells) John Jones Bob Smith Jane Morgan to this: John Jones Bob Smith Jane Morgan
View Replies!
View Related
Search For Two Cells In A Row In Column A
I need to build an if statement around whether or not two blank cells in a row are found in column A moving down from A1 The two blank cells will be between cells that contain data (so no I am not looking for the last cells in the column, matter of fact, the if statement cannot be true based on two blank cells at the end of the column, only inbetween data) Suedo If Two blank cells in a row are found between cells that contain data in column 1 do this Else Do this
View Replies!
View Related
Column Information To Cells In Row
I have column A full of test numbers, and column D is revisions the tests show up in (4 different versions). I would bring this spreadsheet down from 1800 rows to around 500 rows by making the info on D appear on one line in columns E,F,G,H, rather than four rows. Example: This _A__|__B__|__C__|__D__| 1001|_____|_____|v1.0 1002|_____|_____|v1.0 1002|_____|_____|v1.1 1002|_____|_____|v2.0 1003|_____|_____|v1.0 1003|_____|_____|v1.1 1004|_____|_____|v1.1 1005|_____|_____|v1.0 1005|_____|_____|v1.1 1005|_____|_____|v2.0 1005|_____|_____|v2.1...................
View Replies!
View Related
Referencing A Row Of Cells In A Column
I have a row of cells on one "Sheet1." I am trying reference these cells into a column on "Sheet2." I know I can reference one cell at a time, but I have a large number of cells in the row on Sheet1 and I know that there has to be an easier way to do it, I am just not excel-savvy enough to know what it is. I know you can copy and "paste special" (transpose), I am just wondering how to do that while referencing. I have heard that the "offset" function can be used, but after extensive trial and error, I haven't figured it out.
View Replies!
View Related
Concatenate Each Row To Cells In Column
I am trying to concatenate multiple lines of comments into one string and place that string in the comment field corresponding to the start of each record set. I believe the code below is close, but I am receiving this error Run-time error '91': Object variable or with block variable not set Debug points to this line rngComment = . Cells(intCounter, 5) I'm not sure what I am doing wrong. Option Explicit.........................
View Replies!
View Related
Latest Value In A Series Of Cells (not A Column Or Row)
am looking to find the most recent value in a series of cells that I define. I have set up a worksheet for each month and I want to only pull out the latest total entered for a particular cell reference (cell B16 in each worksheet has the number I need). I tried using : =LOOKUP(9.99999999E+307,Jul:Jun!B16) but that just gives me a #value error. I had a search through the archives too and found [url] and [url](neither of which I really understood or could use, although I did have a go), anyone got any ideas? Am sure it's very easy! Just to clarify, I have 12 worksheets, each with a different month. Each month I enter data into the relevant worksheet. I want a running total on a summary worksheet and need to pull out the latest numbers automatically (they don't need to SUM or anything).
View Replies!
View Related
Color Cells Where Column X Same Row Has Text
I've got a spreadsheet where there are three columns (a-d) Now B-D contain dates and I'm looking for A to be coloured conditionally so if column B contains any text, then A's background will be red, if A & B contain text, then it'll be orange and if A & B & C contain text then it'll be green. What I can't seem to work out is how to get this to work in this order only - ie my version with conditional formatting means if someone entered text in D and nothing in B & C it'd turn Red...
View Replies!
View Related
Loop Through Cells On 1 Row Of Column Range
I am trying to create a loop in which the code "reads" ... lets say... cell F5. Determines if cell F5 = 1. if not, it moves on. only, instead of moving to cell F6, it moves to cell G5. so i need to add one to the column letter. How is this best achieved? having trouble with this. adding a letter to it concatinates them, and adding numbers, well its a mismatched type so that simply does not work.
View Replies!
View Related
Copying Cells To The Neighbouring Column In Pairs Along Row 1
So I have a spreadsheet that has a Title in Cell A1, then entries in B1, D1, F1, H1, J1, etc... with empty cells between. What I would like to do is copy those entries to the right, i.e. B1 into C1, D1 into E1, F1 into H1, but all the way along because in my master sheet there are a lot of columns.
View Replies!
View Related
Reference Column Cells And Add The Total From Row
i'm tracking hospital census with days of month in columns and patients 1 - XX in rows. this is basically what i'm working with. A...........B........C...D...E...F...G...H...I...J...AG...AH Patient...Payor...1...2...3...4...5...6...7...8...31...Total 1...........MCR.....x...x...x...x...x...x...d...............6 2...........COM.........x...d..................................1 3...........SP............s...s...s....s...d....................4 4...........MCR.............................x...d..............1 5...........MCR.............................x...x..d...........2 Total.................1...3...2..2...2...3....1..0.........14 The "total" cell formula is =COUNTIF(G7:AK7,"X")+COUNTIF(G7:AK7,"s") S and X just identify where that patient is med/surg vs. skilled. I'm looking for a solution that will reference column cells B2-BX and add the total from that row (AH) if the payor of that row is MCR, then another cell that would do the same for SP, COM and so on. so in this scenario it would be something like COM...7.1% (SUM col AH where col B = "COM" ie. 1/14) SP......28.6% (SUM col AH where col B = "SP" ie.4/14) MCR...64.3% (SUM col AH where col B = "MCR" ie.1/14)
View Replies!
View Related
|
TRACKER
