Highlight Column And Row Of Active Cell But Retaining Previous Formats
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.
I have a template A that has lines on it, sometimes I want to overlay template B which has other lines on it, however when I past template B on top of A the formats of A are overridden by the formats of template B.
Is there any way of "overlaying" B on top of template A without losing the template A formats?
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."
Cells a3 and a4 may contain a number less than number of digits specified above but are custom formatted 000000 and 0000 respectively to ensure they have the correct number of digits. My problem is when I try to combine the four cells they lose their formatting. I have tried =A1&A2&A3&A4 and = concatenate(A1,A2,A3,A4). But both formulas loose the formatting from the individual cells.
I found this code to highlight the active row. I tried to make it highlight the row and column, but I was not successful. What I really need is to highlight the active row and column above and to the left of the active cell, not the entire row and column. For example, if G10 is active, the highlighted cells would be G1:G10 and A10:G10.
Private Sub Worksheet_SelectionChange(ByVal Target As Excel. Range) Dim i As Long Cells.Interior.ColorIndex = xlColorIndexNone If Application. CountA(Target.EntireRow) 0 Then i = Target.Row Else For i = Me.UsedRange.Rows.Count To 1 Step -1 If Application.CountA(Me.Rows(i)) 0 Then i = i + 1 Exit For End If Next i End If Rows(i).Interior.ColorIndex = 6 End Sub
Also, I have fill colors on the sheet and I just noticed that the code removes those fill colors. I need it to not remove my fill colors. The only fill colors it should remove are ones it previously colored.
I need to locate the address of the previous active cell without triggering a selectionchange event (as this is where my code is activated from). Here is what I am currently using unsuccessfully:
whenever the active cell is within a given range, highlight the cell on the same row in column S (by changing its interior colour). This should occur each time the active cell is changed, whether by cursor keys or mouse. The effect would be similar to the row and column highlights at left and top of the worksheet.
This action should be restricted to one sheet in the workbook.
So it's pretty basic. If we simply remember the first ColumnA and ColumnB data, go to the next row, if ColumnA is the same, then ColumnB should be the same. If it's not, then fill with red, go to the next row, repeat. if ColumnA changes, remember the new pair, go to the next row and compare again.
I've thought about this from a scripting perspective, and imagine that something like this would do the job:
Code: sub FindBadTermID () Dim row As Integer Dim dataA as string, dataB as string Set row = 2
1. if there's a value in name or names count => must be at least one value in test1 or test2
2. last value in names count must be equal to the sum of trues in B1:B4 Problem Is it possible to identify once any of the cells in A9:D12 are activated that once that range is deactivated I run the check to make sure the constraints are met, and populate default values in the case they are not?
Is it possible to identify once any of the cells in A9:D12 are activated that once that range is deactivated I run the check to make sure the constraints are met, and populate default values in the case they are not?
Trying to figure it out with a some combination of: checking if the last cell I was in was in the range and I am no longer in that range; using the selection change change event?
Is it possible to have a specified shading (say 50%) applied to all rows except the currently picked row and the header rows to allow a user to focus on inputting across the row? I'd use this in conjunction with " Move Selection After Enter" to "Right" so the user would stay on the same row. I've tried the Help function, but can't find anything.
When looking at a spreadsheet with many columns, it would be nice of the row of the cell I have selected would remain highlighted in yellow, so that as I scroll across rows it's easy to view the data associated with the selected cell. Of course, if I select another cell, I would want the highlighting to change to THAT row. I'm guessing this would have to be in a this.workbook change event.
When I open any spreadsheet (new or already populated) the black box that normally frames the active cell is missing (showing which cell you have selected). Likewise when I 'select all' the sheet does not become shaded.
In addition:
Cannot fill series Cannot change cell format (specifically to show Currency or Accounting w/$ symbol)
Is there something built in to excel that highlights the cell or cells that are currenyl selected and then not save any highlighted cells when the sheet is saved. Maybe if not something that is built in but a vba script?
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.
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 .......................
I have a very large table with 7000 rows and each time I open the file it takes excel about 1 minute to open it.Initially all cells in the rows are empty. Each row in the table has the same formats and formulas(eg. in J106 I have =IF(C106=1,1,"") and in J107 I have =IF(C107=1,1,"")).So the excel allocates all the memory and does the formatting and computations beforehand. How can I add new rows(preserving the formats and formulas of previous ones) dinammycally and automated? An example will be if I start enter data in a row add another row at the end of the table or a button that when I press it adds lets say 100 new rows in the table. Below is the table, the rows in the table to which I add data start form C15 to C7014.
The problem is when I highlight a row with some color the original color of the row is gone, so I tried this code, and again, it's removing the original format and color for the row This is the code from McGimpsey & Associates : Excel : Highlight row with background colors
Code: PrivateSub Worksheet_SelectionChange(ByVal Target As Excel.Range) Const cnNUMCOLS AsLong=256 Const cnHIGHLIGHTCOLOR AsLong=36'default lt. yellow Static rOld As Range Static nColorIndices(1To cnNUMCOLS)AsLong Dim i AsLong IfNot rOld IsNothingThen'Restore color indices
[code].....
How can I retain the range's historical color so that when I deselect the row it reverts properly?
I would like to link a macro to a button on the active sheet, that saves the active sheet In a New Workbook, with Format And Values Only. Preferably saved in the same location as the origonal workbook. This is my main goal.
If possible I would like the new workbook to be named from a chosen cell (E19) on the active sheet with date added.
I'm listing through sheets and have Sheet2 for drop-down lists.
Sheet2 is not visible, but when I click on Cmdbutton It appears so that I can change values in drop-downs. When I hide Sheet2 again, I want to be back on sheet before Sheet2.
I have multiple excel workbooks open at the same time. I need to run a macro on one of them every 15 min but I need to return to the active window when the macro is done. How do I read what window is active then return to it when the macro is done?
Sub AutoSave() dTime = Now + TimeValue("00:15:00") Application .OnTime dTime, "AutoSave" Windows("data.xlsm").Activate Sheets("Data").Copy Application.DisplayAlerts = False ActiveWorkbook. SaveAs Filename:="c:excel" & Format(Time, "hhmmss"), FileFormat:=xlCSV ActiveWorkbook.Close Application.DisplayAlerts = True Windows(1).ActivatePrevious End Sub
I have a column of about 1000 records. In each field is a long integer, that has been obtained through a concatenation formula. I have copied the results to a new column and used "paste special, values" to do so. I now have the results in a new column, but the cell format is text and is giving me an error. Even if I pre-format the cells that I will paste into to be a number they do not stay as a number after being pasted into, they still have an error.
I need to run a vlookup on these results but because of the error in the cells the vlookup wont work until they have been changed to a number format. I am able to change the format of the cells to number, no deciaml places, one by one. I have about 1000 records and need to be able to change the cells to number format in one go, as it is too time consuming otherwise.
If I change the format of the cells all together in one go to a number, they do not change unless you double click in them and press enter one by one. Is there a way to change the cell format for all the cells in one go without this laborious process?
I have data in "column F" that I want to highlight. The data is Listed verticaly from Row 1 to 424 in Column F; I want the spread sheet to hightlight the number when it exceeds the previous days 30,60, and 90 day average volume. How I can make this occur?
Need to be able to highligh data within a range where the date in a column is for the previous month/s- therefore need to be able to also include in macro entering the current month - don't want to have to edit macro each month
do a conditional format rule that will highlight every other row of my excel spreadsheet when the value in a specific Column (say Column A) differs than the previous.
I've attached a sample worksheet with what I want it to look like after the conditional format rule is applied (every other row highlighted in light blue). The rule needs to apply to all rows in the worksheet beginning with ROW 2 (I don't need the rule to apply to the column header which is in ROW 1).
let's say I run a macro from a button on sheet 3, macro process on sheet 10. Is there a way that the macro would automatically go back to sheet 3? Like a "Back" button on IE. I can't code Sheets("sheet 3").Select because I want it to do the same thing on sheet 4,5,6...
I found some VBA code that saves the previous value entered into a cell and displays it as a comment. I've attached that workbook as an example, but I want to change 3 things.
1) I only want to capture the previous values from Sheet1.Column1 (you'll see the example captures changes on the whole sheet1). 2) I want to 'paste' the previous value of Sheet1.Column1 in the corresponding row of Sheet1.ColumnD (i.e. the previous value of A5 will be displayed in D5). The code currently pastes the value in the corresponding address in sheet2. 3) I do not need the comments part. Simply displaying the previous value in the corresponding row of Sheet1.ColumnD is good enough.
