BORDER Around Active Cell
I have a spreadsheet with DDE formulas in the cells. When the contents of any cell changes I want to DRAW A BORDER (NOT CHANGE THE BACKGROUND COLOR !!) AROUND THE CELL .. When another cell's value is updated I want to REMOVE the border which was around the"OLD" cell and now place a border around the most recently changed cell, etc.
Only the newest active cell should have a border around it !!!
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Cell Border Non-visible?
I have seen spreadsheet where the user has made the outline of the cell border non-visible. This seems like a fairly simple task, but I can't seem to find out how you format this type of border.
View Replies!
View Related
Code For Cell Border Styling
I have an excel sheet that is pulling data from other sheet based on some cretaria...i want a macro that when ran on this excel fixes column width , makes cell border as "dotted" and outline the area with Thick Outline. This is what a macro code should do ; Hope this makes sense...thanks. 1. Finds numeric values in rows falling in colum B,C,D,E , than border those cells as dotted (i.e. right click on cell - format column - Border - Style - 3rs on top left row). 2. After filling cell border style as mentioned above , macro should do a THICK BOX BORDER around a cells falling above dotted cells.. i.e. one big sqaure outlined contaiing dotted cells with numeric values. 3. Trick part , my sheet is divided into 2 parts , INTERNAL and EXTERNAL. So i have writen "INTERNAL" in A1 cell and EXTERNAL in 'A' row in last row after INTERNAL lines. So the THICK BOX BORDER as requested in pt 2 shud be separted ones for INTERNAL and sep from EXTERNAL.
View Replies!
View Related
Cell Border And Colour With Text
I have a sheet which automatically adds entrys to the sheet depending on what team they are on, all i really want to do is colour and border to the last row only, i know this should be able to get done in conditional formationing some how but im unsure. I have got it in some of the cells with text in, but the ones with numbers in remain the same, if i can remember i used con formating and done, if the cell value is greater than <> then my formating, but numbers remain the same.
View Replies!
View Related
Automatically Format Cell Border
I am trying to Formal my Cell Border automatically. Lets say i have cells A B B A A A These are single cells running downwards only. I want to format this 6 cells into 1 box using (outside borders) formating. I need to repeat this for every 6 cells into 1box...and continue on and on. Im doing this process manually...But i got like 100,000 cells...
View Replies!
View Related
Changing Colour Of The Border Of A Cell
How can I change its colour? Every time I try to it reverts to the automatic black. I do not want to change the automatic colour (not that I know how to) because black suits me for most of the cells; but there are some where I want a blue border. This problem arose in conditional formatting but applies to ordinary formatting too. It occurs in 2007 and 2003.
View Replies!
View Related
Format Border Based On Cell Content
I have a dynamic table (rows regularly added or deleted) that is generated automatically based on choices made by the user in another worksheet. I need a function that will examine all the cells in one column, and when it finds a difference between two cell entries, it will change the border between these two entries to dark (medium weight), and then extend this border across the table. The end result should be that wherever the cell contents differ within a column, the table is divided by a darker border across the entire row.
View Replies!
View Related
Bottom Border Depending On Matching Cell Value
I want to add borders to a range depending on the value in the "a" column. Normally I want a left border in cell "a", a right border in cell "h" and bottom border in cells "a" to "h". However if the value in cell "a" is the same as the cell below I do not want the bottom border. Cell "a" contains the names of customers using our facilities each day. Some customers use several facilities on the same day. I want the borders to encompass all facilities used by each customer per day e.g. range "a1:h8" might need bottom borders on all rows, because they are single customers using only one facility that day but ranges "a9:h11" would require bottom borders only on row 11, because that customer is using three of our facilities that day. Set Rng = ActiveSheet.Range("a:h").SpecialCells(xlCellTypeConstants) change the Range to "a:h" and:- With .Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With .Borders(xlEdgeRight)..........
View Replies!
View Related
Cell Border Not Moving With Cell When Sorted
how to filter rows of data, in ascending or descending order, whilst still retaining formatting (in my case cell borders) and conditional formatting? At present, when I filter rows in ascending / descending order, the cell formatting & conditional formatting stays in it's original position, rather than moving with the cell. I can't find a way to resolve this.
View Replies!
View Related
Double-Click Cell Border Jumps To Other Cells
I'm trying to double-click a cell to edit the contents, and I am off a few pixels, it treats the double-click as a double-click on the cell border, not the cell contents, and the focus jumps to another cell in the direction of the border instead of going into edit mode. The effect is as if I had pressed Ctrl and the arrow in the direction of the border I clicked on. The only way I've found to disable this is to turn off the option "Enable fill handle and cell drag-and-drop", but unchecking that also disables actually useful things, like dragging the corner of a formula call to copy it down, etc. Is there a good way to disable this cell jumping when I accidentally double-click a cell border?
View Replies!
View Related
Find Method To Search For The Active And Non Active Values
I have a range of amounts in Sheet 1 from F7:Q13 and im using the find method to search for the active and non active values in the cell. Which means that if there's a value in the cell it will transfer the value in Sheet 2, if nothing is found in the cell the cells in Sheet 2 will return as nothing or null. I think the problem lies on the FindWhat variable. Im getting a compiled error which im not sure what is it. I've attached the spreadsheet so you get a better idea of the problem that i encountered.
View Replies!
View Related
Identify Active Cell And Use The Column To Add Formula To Another Cell
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 Replies!
View Related
Return Maximum Of Active Cell & Arbitrary Value To Cell
I am looking for VBA that will add the value of the current active cell on the sheet to the value in cell F12. The maximum value of F12 cannot exceed 1000. So if the value in F12 = 950 and 100 is the value in the active cell the maximum value in F12 should show 1000, not 1050. It should do this on the click of a button.
View Replies!
View Related
Go To First Cell In List That Matches Contents Of Active Cell
I am just learning to use VBA and this may be the most simple task ever, but I can't figure it out. I've searched for all of the keywords I can think of, but can't find a solution... I have a list of names in a sheet. Other columns in this sheet contain data like amount charged, amount paid, etc. This sheet must be manually updated (because the other program won't export the information I need) periodically to ensure proper billing/payment application in the original software (all transactions are handled by other people that I don't trust). I sort the list so that the all names that are the same (ie John Doe) are together. A short example list looks like this: Jeremy Apple John Doe John Doe John Doe Jimmy Kravitz Jimmy Kravitz In updating my sheet, I set up a macro that will input todays date in one of the columns for all occurences of that name (so, every row that contains John Doe in column B, column V will have todays date in it). Currently, in order for my macro to work properly, I have to manually make the activecell the first occurrence of 'John Doe'. When I'm ready to update 'Jimmy Kravitz', I have to select the first occurrence of 'Jimmy Kravitz' and so on. Here's my question - Is there a way to use a VBA macro to find the first occurrence of 'John Doe' (and automatically 'know' which name I am updating)? Basically, I need a macro that will take the information that is in the cell in column B in the active row, find the first row that has that same name, and make that cell (column B) the active cell...
View Replies!
View Related
Add Active Cell Data To Cell By Clicking
I want to automatically put information from several cells into one cell, when these cells are clicked. Let's say that A1:J8 is the range were a cell can be clicked and if a cell is clicked in that range the value of these cells should be put in cell A9. If possible I want to avoid private subs in a worksheet. I preffer to have a macro running, so I can decide when to stop this routine.
View Replies!
View Related
Active Time In A Cell
is there any way to have a running clock or time in a cell like when you open up a sheet you see it click-by second by second I would like this, so when i print the sheet it shows exactly what time it was when printed
View Replies!
View Related
Active Cell And Offset
ThisWorkbook.Sheets(2).Cells(nrow + 1 + j, 2 + high_count).Formula = "=VAR(" & ActiveCell.Offset(0, n_high_count -1) & "," & ActiveCell.Offset(0, -1) & ")" *n_high_count is the negative of high_count I want "ThisWorkbook.Sheets(2).Cells(nrow + 1 + j, 2 + high_count)" to have the formula "=VAR(XX:XX)" where the range is the current row second column to the current row current column -1.
View Replies!
View Related
Copy Active Cell
I have two worksheets with the same column. Would like to edit ex. sheet1 A1 and that sheet2 A1 updates automatically by copying just the value, not the reference.
View Replies!
View Related
Reference Active Cell
I've got 3 columns say A,B,C. Column C (row1) = Column A (row1) * Column B (row1). I'm trying to apply this formula to all the cells in Column C. I've tried typing the following formula in all of column C. =OFFSET(ActiveCell,0,-1) * OFFSET(ActiveCell,0,-2) Obviously the ActiveCell reference doesn't work. This would be simple in VBA but here on excel how would I reference to the cell itself where the formula is contained?
View Replies!
View Related
VB - Paste In Last Active Cell
I'm working on this Macro process - and I can't figure two things out. 1) How do I get it to past contents in the second blank cell at the bottom of a list in a specific column? IE - data in B1:B17, paste new list in B19. What I'd REALLY like, is for it to paste in B8, but if B8 used, THEN paste to the second to last blank cell in column B. (if thats even possible) 2) I have VB open an excel workbook, grab data, copy and then past into 1) (above), but then close that workbook. However it always asks "do you wish to save?" and "Do you wish to keep data on clipboard" How can I program it to say "no" for both of these windows prompts? Here's an excerpt of my code:
View Replies!
View Related
Finding The Active Cell
how to simply find out what the active cell is (This would be the cell the curser is currently in). It is already displayed in the Name Box on the menu in Excel, but I can't find a way to access this data automatically.
View Replies!
View Related
Use Value Of Active Cell To Name Row
I'm trying to take the value of a cell and use the value as a name for the row. If cell a1 has value = June. I want to change the name of row 1 to June. I'm not sure what I'm doing wrong with the following code. Sub Name_a_row() ' ' Dim TheName As String Dim RowNum As Integer TheName = ActiveCell.Value RowNum = ActiveCell.Row ActiveWorkbook.Names.Add Name:="TheName", RefersToR1C1:="=Data!R&RowNum"
View Replies!
View Related
Pasting To Last Active Cell
I'm trying to paste columns to the last active cell on another sheet and I'm doing something wrong. Sub WPRFilterMacro() ' ' WPRFilterMacro Macro ' Filter and copy and paste WPR information into WPR sheet ' Sheets("Data Sheet").Select Range("A1").Select Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="Work Place Resources" Range("B2").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("Cisco(WPR)").Select 'This takes command activates cell G7 after being on D5, don't know why ActiveCell.Offset(1, 0).Range("D2").Select ActiveSheet.Paste I'm trying to paste this column to the first non-active cell in column D, but the code is taking it down two rows and two columns down, depending on where the active cell starts. I would just start it on a certain cell, but eventually, there will be different starting points.
View Replies!
View Related
Active Cell As Reference
I would like to fill data in table on another worksheet, lets call it Sheet2 that starts with the activecell from Sheet1 and then use offsets. The activecell changes, but the remaining offsets for rows and cells always remain the same. I am pretty sure , I can figure it out this out using VBA, but is there a formula way to do this? Either looking at the activecell from Sheet1 as a start or using formatting like bold the start cell?
View Replies!
View Related
Change Active Cell Color According To Name Above
Here is a screen shot of what I have: I want to be able to enter the name from the above 4 cells (B3,B4,B5,B6) IN THAT COLUMN, and have the active cell change color accordingly. If I am in cell C7 and start to type "PUBLIX" It should turn red in color. It does not because the code looks to a specific cell (example: B3). What can I change in my code to give the results I desire? I have conditional formatting code below I copied from THIS site: Private Sub Worksheet_Change(ByVal Target As Range) ' Multiple Conditional Format Dim rng As Range ' Only look at single cell changes If Target.Count > 1 Then Exit Sub ' Adjust Format range to suit Set rng = Range("B7:H74") ' Only look at that range If Intersect(Target, rng) Is Nothing Then Exit Sub ' Adjust conditions to suit Select Case Target.Value Case Range("B6").Value Target.Font.ColorIndex = 50 ' Green Case Range("B5").Value Target.Font.ColorIndex = 3 ' Blue ' Case "Super" ' Target.Font.ColorIndex = 6 ' Yellow Case Range("B4").Value Target.Font.ColorIndex = 13 ' Purple-ish Case Range("B3").Value Target.Font.ColorIndex = 5 ' Red ' Case "Corporate" ' Target.Font.ColorIndex = 37 ' Light Blue End Select End Sub
View Replies!
View Related
Sort By Active Cell Column
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 Replies!
View Related
Insert Rows At The Active Cell
I want to be able to allow a user to insert a number of rows (they would specify the number) into a worksheet, at the active cell location. I also need the same number of rows to be inserted into the same worksheet (but much lower down) The location of which is to be governed by the location of the original inserted rows. Having created the new rows I need all the formulas from the row ABOVE the active cell to be copied into all the newly created rows. example If active cell is A5 I want user to be able to run a macro which asks them how many rows they wish to insert. (Assume the user asks for 7). The macro then inserts that number of rows, (starting at row 5). And additionally adds 7 rows at a location further down the worksheet (say row 105). The formulas from the row above the active cell are then copied into all the newly created rows. 2nd example If the active cell was A9 then the macro would add 7 rows, starting at row 9 and additionally add 7 rows starting at row 109. Then formulas added.
View Replies!
View Related
Print Active Cell Highlight
I have a routine that highlights a selected Active Cell, but when I printout the spreadsheet this highlight is not printed. I have been pull my hair out (what's left!), to try to show this highlighted cell when printed. Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Intersect(Range("d22:u45"), Target) Is Nothing Then Range("d12").Value = " Out" Range("e14").Value = "0" Else If Intersect(Range("d22:u45"), Target) >= 3 Then Range("d12").Value = Cells(19, ActiveCell.Column).Value Range("e14").Value = Cells(21, ActiveCell.Column).Value Static rngPrev As Range, PrevColor As Integer Dim TempColor As Integer TempColor = Target.Cells(1, 1).Interior.ColorIndex If Not rngPrev Is Nothing Then If PrevColor <> xlLineStyleNone Then .......................
View Replies!
View Related
Finding Active Cell After Blank
After sorting data I have a column starting in G3 that contains blank cells at the top and then data. 1. I need to find the first 4 active cells that contain data in column G, and then copy the corresponding data contained in columns A,B and C. 2. I then need to paste the data from these 3 columns ( A,B,C) to a Cell starting at R16
View Replies!
View Related
Become The Active Cell Than The Userform Is To Open Up
I have created a user form that has 6 input boxs and at the bottom of the userform has a seventh box that has a running total. I have two buttons one to "accept" and one to "cancel". My prloblem is creating the code to make it work correctly. 1) if a cell in B5:B20 become the active cell than the userform is to open up. 2) once the useform is open the user can input up to six different numbers into the input box's which will show the runinng total in the seventh box. 3) Once all numbers are inputed into the input box's the user can hit "accept" to post the total into the active cell or hit "cancel" and nothing have nothing happen.
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
Active Cell - Selecting Too Many Cells
I have some code as below Range(ActiveCell, ActiveCell.End(xlDown)).Select This should basically go down a list and stop when there is a blank cell. However due to a previous operation in my excel program, it is not doing so. Basically this list has been formed from copy and pasting from a list of formulas. some of these formulas return a result and others return "" leaving the cell empty. When i have pasted i have used paste special/values... but it still seems to paste something into these cells because this macro wont work. I even went and manually selected the first blank cell and pressed the delete button and when i do this it correctly selects the cells only with content, so clearly when pasting a "" value it stops the activecell operation from working..
View Replies!
View Related
Delete N Rows From Active Cell
Is there a quick way to delete "N" rows from the active cell regardless of the content of the cells in those rows? I import text files into excel then frequently have to go through them and delete rows of nonimportant data. For example if cell A57 is currently selected can I run a macro that will delete the next 10 rows?
View Replies!
View Related
Move Active Cell One To The Right - 2000
What is the command to move the active cell? For example, suppose C15 is the active cell, and I want a macro to move the active cell one to the right. Or maybe 3 to the right, and one down. The deal is, I'd like to be able to make the active cell move in a fixed pattern relative to the cell that is already active (before the macro is run), rather than moving to a final fixed destination (which would be something that even I could figure out). In other words, if I select R20, and run the macro (or whatever other mechanism you guys come up with), then R21 will be selected; likewise, I can click G12, run the macro, and G13 will be selected.
View Replies!
View Related
Active Cell Value In Textbox Code
I am trying to do the following. When Userform2 fires up - there is a textbox. I would like the textbox1 to be filled by: Taking the Selected or Active Cell in Sheets("positions"), eg, L5 but then finding the exact Cell match in Sheets("price"), eg, also L5 and placing this cells Value in Textbox1. The 2 sheets are exact mirrors but contain different data.
View Replies!
View Related
Create Hyperlink In Active Cell
I'm running Excel 2003 and I'm looking for assistance with programmatically creating hyperlinks. I have to update a Shared Workbook on a weekly basis. The cells in column D contain a catalog id and I need to create a hyperlink that includes this id in the url. What I want to do is insert a new row, enter the catalog id and then create a hyperlink that contains the id. Additionally, the text to display has to be the 8 digit id. Here is an example of the hyperlink.
View Replies!
View Related
|
TRACKER
