Highlighting Active Cell's Row, Along With Any Row That Shares Same Value In That Column
Sep 25, 2009
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 9 Replies
ADVERTISEMENT
Jul 28, 2014
I am looking for a way to have the active cell row and column (standout). I did have it highlight but as users tried to highlight certain cells the active highlighting of the rows and columns would erase the color. So they are asking for something that can take its place with an on and off button to it.
View 5 Replies
View Related
Nov 30, 2006
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 9 Replies
View Related
May 10, 2013
I am trying to create a VBA code that will automatically highlight the row of an Excel table (2007) of the active cell. So far I have this:
Code:
If Not Intersect(ActiveCell, Range("Table_Name")) Is Nothing Then
Range("A" & Target.Row & ":EJ" & Target.Row).Interior.Color = 10092543
End If
Where the columns A and EJ represent the size of the table. But I would like to automate the column choices so that if the table has columns added or removed, the code still works. Is there a way to just reference a row of a table?
View 1 Replies
View Related
Nov 18, 2009
Using excel (i think its 2000, work PC they are behind the times) and i was wondering if there is a way to automatically highlight the active field as i move around the cells.
It would be easier to see where my cursor is.
View 9 Replies
View Related
Feb 11, 2014
I am looking at some combination of Index, Match and array functions in order to assign a value, in this case a currency, based upon an instance where another data item with the same trade ID already has that currency assigned to it.
Basically I have a list of data where there are a list of trade IDs for each data row/item.
In some cases there will be two rows of data that have a common trade ID.
For example there may be two results that share a trade ID eg 450222, one in EUR, one in another currency eg GBP.
What I need to do is create a formula in other column to the right of my data that recognises that if a trade ID is assigned to a non-Euro currency then to apply the foreign currency to both trades with the common ID.
ie change
Ref 450222 Ccy EUR
Ref 450222 Ccy GBP
Ref 450223 Ccy USD
Ref 450223 Ccy Eur
to
Ref 450222 Ccy GBP
Ref 450222 Ccy GBP
Ref 450223 Ccy USD
Ref 450223 Ccy USD
An offset function can't really be applied as the order of the data is random. My subsequent calculations involve a SUMIFS on GBP, USD etc.
View 1 Replies
View Related
Apr 6, 2008
A worksheet called "purchases" contains the list of shares purchased during the period 1.4.5 to 31.3.8. It has four columns :
1. Date
2. Name of the share (name is repeated for every new purchase as it is entered in a new row and is totally independent of the earlier entry of purchase)
3. Qty
4. Sale price
Likewise there is a sheet for sales also. I want to do the following:
a) Find out all the records showing purchases of a particular share and add them up (both quantity and value). And thus, make a list of shares showing aggregate purchase of each share during the entire year. Likewise, I want to do this for sales also.
b) For every share that has been sold within one year after its purchase, apply 20% tax on the profit earned on sale of such share.
Additional information: If 100 shares of A Ltd are purchased on 1.4.5 and 200 shares on 1.5.5 then during the sale of 150 shares of A Ltd on 2.4.6, 100 shares purchased on 1.4.5 and 50 shares purchased on 1.5.5 shall be deemed to have been sold.
View 5 Replies
View Related
Apr 7, 2009
I have a macro where I sum a large number of cells in column AZ. How can I have the Macro end in the last cell of column AZ where the sum is located? The length varies in each file.
View 3 Replies
View Related
Jan 15, 2010
I use the Index/Match formula to find the last active cell in a column quite effectively.
I'm wondering though how to adapt it to find the second last active cell?
EX: Last active cell formula:
Data:
a 10
b 11
c 0
d 12
View 10 Replies
View Related
Apr 23, 2014
I want a simple macro which will go to the cell in row one in the active cell's column
View 2 Replies
View Related
Jun 28, 2009
In my macro, I need to, from any column, move the active cell to column A, while remaining in the same row.
View 2 Replies
View Related
Dec 2, 2008
Is there a formula I can use to locate the last active cell in a column. I did use the count function, however this isn't always reliable if there are blank cells within the column
View 5 Replies
View Related
Jul 7, 2006
I have a code which looks at sheet2, useing a date value, and returns the Vent value and the Tanker value to sheet1. Because of the file size I have pasted a small section of Sheet2 in the hopes you can see what I am doing. As can be seen my code is not going to work with Offset as soon as the Data fills further down the sheet(every 2 weeks). I need a way to reference the active cells column name (it is a named range) or header name. Both are the same. That is the header name is the same as the name of the range. there is a way to perhaps find the first cell of the column,which I guess would work,but have reached a stale mate with what I've tried so far.
Vent 01Vent 02Vent 03Vent 04
Date3/06/063/06/06n1/06/06
Tanker8248824617606
WhoJ AshJ AshEmptyHarry
Date
Tanker
Who
Private Sub DueCommandButton_Click()
Dim strRecordID As Range
Dim rngData As Range
Dim Vents2 As Range, Tanker_Result As Range
Dim wsheet1 As Worksheet, wsheet2 As Worksheet
Dim Column_Name As Range
Dim ActiveCell As Range
Set wsheet1 = Worksheets("Sheet1")
Set wsheet2 = Worksheets("Sheet2")....................
View 2 Replies
View Related
Dec 22, 2006
I've been racking my brains trying to figure this out but cant, please can someone assist. I need to write some vba code that looks at cells in column aa and ag (on the same row) and displays a message is ag > 0 and aa <> "Agency".
View 8 Replies
View Related
Jan 7, 2007
I am trying to perform a sort based on the ActiveCell.Column
I thought my code would exclude the hearer rows, but presently it moves the header rows beneath the data
I tried
Header:=xlGuess as well as
Header:=xlNo
Same result
What am I doing wrong?
Thanks
-marc
Private Sub comp_mySort()
Selection.Sort Key1:= Cells(1, ActiveCell.Column), _
Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub
View 9 Replies
View Related
Jan 8, 2007
I have a sort procedure I have been working on. Sort By Active Cell Column
Now I would like to make sure the row of the activecell.column is row 7. I tried
Private Sub comp_myMonthlyReport_SortAscend()
Dim rng As Range
With ActiveWindow
rng = .ActiveCell(7, .ActiveCell.Column)
End With
Selection.Sort Key1:=rng, _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub
But I receive this error: Run-time error '91': Object variable or With block variable not set
View 6 Replies
View Related
Feb 14, 2012
I use this macro to open a hyperlink in "column B" of the next row. However, it only works if I begin the macro from "column N" on the line above. (the hyperlink is always located in column B)
I want to be able to run this macro from any cell on the line above. How to modify it?
Code:
Sub Open_Hyperlink()
'
' Open_Hyperlink Macro
'
' Keyboard Shortcut: Ctrl+o
'
[Code]...
HTML Code:
ABCDEFGHIJKLMNO
1ActiveURLWhatDateFirst NameLast NameOtherOther2Other3Street1CityStateZip
2XLinkData112/21/2011BobSmithData2Data3Data4123 MainMooresvilleNC28117
3XLinkData112/22/2011LarryJonesData2bData3Data4456 MainMooresvilleNC28117
4XLinkData112/23/2011MaryAkinData2Data3Data4789 MainMooresvilleNC28117
An example would be to run it while Cell "I2", "J2", or "K2" is selected and have it open "B3"
View 2 Replies
View Related
May 16, 2013
How to find the column based on my active cell...if my active cell is in A1 then it pops up saying your in column A.
View 3 Replies
View Related
Apr 11, 2007
The attached sample workbook has a listbox of areas in a userform. The Textbox on the user form is so that the user can edit the Area names in the listbox. The userform functions fine as long as the column that contains the area name is not hidden. What I want to be able to do is edit the Area Names using the Textbox with the area name column hidden.
View 2 Replies
View Related
Dec 1, 2009
I have a range of unlocked cells (B5:S10) that users enter data in. This sum of this data is then charted. The formula (sum) in a cell equals zero even when there is no data entered by the user. This zero is then charted.
I need to be able to plot the zeros if the user enters zeros but not plot the zero if the cells are blank.
What I was attempting to do is to use the worksheet change event to add the formulas to a cell so that the chart does not plot the value until something was added.
In my change event I need to know that a cell in the range (B5:S10) was changed and that if it was D7 (for example) that I need a formula enterd in D11 [=SUM(D5:D10)]. If it was I5 then the formula would have to go in I11 [=SUM(I5:I10)].
View 8 Replies
View Related
Apr 4, 2013
Is there a way to highlight the row and column of the active cell? The highlighting will move as the active cell is moved around the spread sheet. I have a newbie manager that is bugging the pea-turkey out of me! He wants to be able to easily see where he is within some of our sheets while he is in a vehicle or outside on a job site...seems like an "accessibility" type issue, but can't find anything in Excel "Options."
View 5 Replies
View Related
Jun 28, 2014
I want a macro which can filter the data on the basis of active cell
View 1 Replies
View Related
Apr 15, 2008
1st post so hope that title isn't too vague.
Using VBA, I have a macro that will find a column based on a week number and add in a new column.
It will then offset the ActiveCell down one.
I now need the macro to SUM all values in that row to the left of the ActiveCell.
My original thought was to use:
ActiveCell.Offset(1, 0).Range("A1").Select
Range(Selection, Selection.End(xlToLeft)).Select
Unfortunately, there are gaps in the data field, blank cells that should count as zero value.
How can I highlight all cells to the left, from whichever column the active cell is in, through to column B?
View 6 Replies
View Related
Feb 15, 2013
I found this spreadsheet on here and I have been trying to customize it to what I need. I am trying to have scores from skins match highlighted. I want only the minimum score to be highlighted but if there is another duplicate minimum score I don't want it to highlight anything. I also need to find a way to count the skins won by each player and have it off to the side.
For those not familiar with golf a Skin is a game where you try to get the lowest amount of strokes on a specific hole. Ex- 4 people play the hole one. P1 scores 4, P2 gets a 3, P3 and P4 get 6. The skin would go to P2 who has the lowest score on that hole.
Highlight lowest number in each column not highlighting if there are duplicates starting at L6 down to L11 and for each column till AC. And the same for the group just to the right on attached file.
On row 13 and 14 it tells me who won a skin. I want to tally up the total skins won by each player. so if Joe's names shows up twice on R14 I want it to tell me somewhere in the sheet Joe = 2
View 6 Replies
View Related
Dec 2, 2009
I have a spreadsheet with my Periods along row 10. e.g. C10: "1", D10: "2", E10 "3", F10: "4", G10: "5" etc. (green on the attached sheet). I have my departments along column B, e.g. B11: "Baked" B12: "Fresh" B13: "Frozen" (yellow on the attached sheet)
what I need and cannot work out is some VBA code that will populate two variables (lets call them Period & Department) when I click on one of the figures. For example if I click on cell: if I click E14: Period would have the contents of cell E10, and Department the contents of cell B14.
if i click G14: Period would have the contents of G10, and Department the contents of cell B14 again. I know how to get the click on the cell to work properly etc, and I have code to slot these variables into that works very nicely, I just can't get this bit to work!!!!
View 2 Replies
View Related
Feb 28, 2014
I have found this code by searching witch is perfect
Code:
If Target.Cells.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
Cells.Interior.ColorIndex = 0
With Target 'With the cell that was selected,
.EntireRow.Interior.ColorIndex = 8
.EntireColumn.Interior.ColorIndex = 8
End With
Application.ScreenUpdating = True
The point is that i want to retain the previous formatting of cells when i select another cell. And also i would like to create a switch to turn the Worksheet_SelectionChange event on and off. The tricky part is here i guess, becouse i would like when turned off to retain the previous formatting also.
View 2 Replies
View Related
Jun 22, 2006
I am attempting to write an Excel Macro that would allow me to Click on a particular row and have the fields in my form fill with the data in the columns of the row.
I am fairly new to Macros and VBA, but know that this has got to be a possibility.
View 9 Replies
View Related
Jul 7, 2014
I am making a spreadsheet in which I have been asked to have certain column greyed-out based on an answer that was given in a dropdown menu. Specifically, a dropdown menu in column D asks if the client was a Youth or an Adult. Depending on what the anwer is, other columns in the spreadsheet will be greyed-out; different ones for each answer. My problem is, I need this to occur for the active row being editted. For example: The first row that the user can input data into is row 4. If D4 is answered with "Adult" then certain other columns are greyed-out. However, if the next time the user updates the spreadsheet with information for a different client (now entering information in row 5) and they select "Youth" for column D5, then different columns get greyed-out. The columns that are greyed-out will depend upon the answer for the dropdown menu in column D for whatever row is actively being editted.
View 5 Replies
View Related
Feb 25, 2014
I work on an excel spreadsheet all day and I'm constantly cuting and pasting a value to filter another column. I would love to have a macro button that would automatically do this.
The job card sheets are labled 'Page 1' through to 'Page 175'. As I am working on each job card sheet I need to filter column on another sheet within the same workbook. This is indicated on the attached picture.
So in a perfect world as I would enter in the PLANT ID number on to any job card, then hit a macro button on the toolbar and the Pole No column would then be filtered by the value in the PLANT ID cell.
As there are multiple job cards the macro would probably need to use the 'active cell' value to filter by. But as you can probably tell I'm no expert so you judge the best way to do it.
MWTS034G22 Job Card Sheet
[URL] .....
Windows 7 (32bit)
MS EXCEL 2010
View 2 Replies
View Related
Dec 3, 2008
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 4 Replies
View Related
