Highlight Column And Row Of Active Cell But Retaining Previous Formats

Feb 28, 2014

I have found this code by searching witch is perfect

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


Retaining Both Cell Formats When Pasting On On Other

Nov 28, 2008

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?

View 7 Replies View Related

Highlight Row And Column Of Active Cell In Large Spreadsheets

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

Retaining Previous Entry In Input Box?

Jul 20, 2014

I have the following code below. I would like to amend the code to retain the previous entry in the input box and amend this if the need arises


ActiveCell.FormulaR1C1 = InputBox("Amend details if different from data in inputbox")

View 2 Replies View Related

Join Digits From Multiple Cells While Retaining Custom Formats

Apr 2, 2009

I have four different cell.

Cell a1 contains 6 digits
Cell a2 contains 1 digit
Cell a3 contains 6 digits
Cell a4 contains 4 digits.

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.

View 7 Replies View Related

To Highlight Active Row And Column

Jan 6, 2008

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
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.

View 9 Replies View Related

Previous Active Cell Address

Jun 3, 2006

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:

strPrevAddress = ActiveCell.Previous.Address

View 4 Replies View Related

Excel 2003 :: Highlight Cell On Same Row As Active.cell Whenever Active.cell Changes

Dec 10, 2012

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.

It's for Excel 2003.

View 3 Replies View Related

Highlight Cell When Row Data Changes From Previous Row

Jul 7, 2013

I have a spreadsheet with over 6k rows, each with over 30 columns.

My focus is in 2 columns. ColumnA is formatted as General, ColumnB as Number.




[Code] ........

I need VBA code that will Fill the cell in ColumnB if it changes within the same ColumnA data.

So the output would end up like this. (Although the cell would be filled, not the text turned red)



[Code] .........

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:

sub FindBadTermID ()
Dim row As Integer
Dim dataA as string, dataB as string
Set row = 2


[Code] .......

View 9 Replies View Related

How To Check If Previous Active Cell Was Within Range

Jun 18, 2014

So the constraints are

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?


Attached File : HelpMeWorkbook.xlsx‎

View 8 Replies View Related

Highlight Active Cell Only?

Jun 8, 2014

Need to have only active cell in in any worksheet highlighted or formatted in a different way than other cells

View 1 Replies View Related

Highlight Row Of Active Cell

Oct 27, 2008

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.

View 3 Replies View Related

Temporary Highlight Row Of Active Cell

Nov 18, 2010

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.

View 9 Replies View Related

Active Cell Highlight Missing

Feb 22, 2010

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)

View 4 Replies View Related

Highlight Active Cell Or Cells

Jan 14, 2014

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?

View 1 Replies View Related

Highlighting The Active Cell After A Highlight

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

Print Active Cell Highlight

Nov 14, 2006

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"
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 9 Replies View Related

Add New Rows(preserving The Formats And Formulas Of Previous Ones) Dinammycally And Automated

Apr 24, 2006

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.

View 3 Replies View Related

Highlight Entire Row When Cell Selected Without Losing Original Formats And Color Of Original Row

Sep 5, 2012

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

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


How can I retain the range's historical color so that when I deselect the row it reverts properly?

View 4 Replies View Related

Change The Background Or Highlight (instead Of Bold Border) The Active Cell

Jul 4, 2007

As i navigate excel, I was wondering how to change the background or highlight (instead of bold border) the active cell?

View 3 Replies View Related

Save Active Sheet As New Workbook, Formats & Values Only

Mar 26, 2008

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.

View 8 Replies View Related

Back To Previous Active Sheet?

Aug 22, 2014

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.

My code for open and close sheet2 :


How can I do that ?

View 2 Replies View Related

Select Previous Active Window

Mar 25, 2009

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"
Application.DisplayAlerts = False
ActiveWorkbook. SaveAs Filename:="c:excel" & Format(Time, "hhmmss"), FileFormat:=xlCSV
Application.DisplayAlerts = True
End Sub

View 2 Replies View Related

Convert A Column's Cell Formats

Oct 10, 2008

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?

View 3 Replies View Related

Highlight Above Previous Average Volume?

Jan 21, 2012

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?

View 2 Replies View Related

Highlight Date If Previous Month

Mar 13, 2008

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

View 2 Replies View Related

Conditional Format Rows To Highlight Every Other Row With A Value Different Than Previous

Feb 11, 2014

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).

View 4 Replies View Related

Automatically Go To Previous / Last Active Sheet After Macro - Like IE Back Button

Jun 28, 2012

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...

View 3 Replies View Related

Sorting Data In A Column Retaining Labels

Sep 15, 2014

I'm trying to sort a column of numeric values largest to smallest while retaining their unique designators and color illustrations.

See attached. 3ColumnSort.xls

View 3 Replies View Related

Save Previous Cell Value In The Corresponding Row Of Different Column In Same Sheet

Apr 4, 2014

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.

View 2 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved