Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Auto Highlight Cells


If I click on cell A1 is there a way to make Cells A12 and A13 highlight in yellow?


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Auto Highlight A Row
I'm trying to reduce my formating time by have a script auto highlight a row based on a certain value in column "K" SPECIFICALLY K9 thru K1009.

Hight Light Row - YELLOW
if this value "AAIT ASSY" OR "AAIT OW" is in column "K" SPECIFICALLY K9 thru K1009

Hight Light Row - LIGHT YELLOW
if this value "AAIT KIT" is in column "K" SPECIFICALLY K9 thru K1009

Hight Light Row - TURQUOISE
if this value "C-TYPE" is in column "K" SPECIFICALLY K9 thru K1009

Hight Light Row- TAN
if this value "C/A" is in column "K" SPECIFICALLY K9 thru K1009

Hight Light Row- ORANGE
if this value "ZZ" is in column "K" SPECIFICALLY K9 thru K1009

View Replies!   View Related
Largest Gap + Auto Highlight
I have this excel spreadsheet and I need to calculate the biggest gap between the numbers below. (The gap cannot exceed 9 rows)

Example:
8/31/07 - 47.32
9/7/07 - 52.41
9/14/07 - 50.63
9/21/07 - 48.72
9/28/07 - 54
10/5/07 - 51
10/12/07 - 48
10/19/07 - 55
10/26/07 - 58
11/2/07 - 85
11/9/07 - 116
11/16/07 - 127
11/23/07 - 200
11/30/07 - 169
12/7/07 - 156
12/14/07 - 143
12/21/07 - 152
12/28/07 - 160
Biggest gap: 152

I cannot manually do every single row (this excel is gigantic). Isn’t there some excel formula to do this for me automatically?

I tried: =MAX(B1:B18) – MIN(B1:B18). Here I get the biggest gap between B1 (47.32) and B13 (200), but this gap is bigger then 1 month = 9 rows, which it is not allowed to exceed.

I was able to calculate the gap by taking the difference between the MAX(B1:B9) and MIN(B1:B9) and then repeating this one row down at a time ( e.g. B2-B10, B3-B11, B4-B12, etc)

This of course takes up a lot of space but does the job. If a shorter way is possible then please let me know. If not then I only need one thing: I need the chosen rows with the largest gap between its max and min to be automatically highlighted.

View Replies!   View Related
Auto Highlight All The Selected Row
Is it any coding can auto highlite all the selected row which contain "#N/A" in a worksheet.


View Replies!   View Related
Auto Highlight Row & Columns
i have a sheet with dates going from J1 to IV1, and names going from C7 to C360

What i am looking for, is when i hover over say Y118, it shows a highlighted line going from A118 to Y118 and Y1 to Y118

If this is not possible, would it be possible to do the same thing when cell Y118 i clicked

This would need to work for all posible cells within this range?


View Replies!   View Related
Auto-highlight A 'below Limit' Number In An Average
I'm trying to create a gradesheet in which any score that falls into the
failing range will automatically highlight in red...

View Replies!   View Related
Highlight Cells On A Form So That A User Knows Which Cells To Fill Out
1. I want to highlight cells on a form so that a user knows which cells to fill out.

2. When they print the form I do not want the cell shading to print.

View Replies!   View Related
Highlight/Flag Formula Cells Referencing Other Cells
I am trying to write a macro which highlights all the cells with any reference to other cell. The macro should highlight a cell with formula like = 3 * A25 but not highlight a cell with following formula. = 3 * 0.4535566. 'Hasformula' property is not useful here as it can't distinguish between above examples.

View Replies!   View Related
Highlight Cells If Match Another Sheet Cells
I have a lottery pool in sheet 1 in which i have the names of the people and their number picks. In sheet 2 i have the lottery results.

All i want is to be able to highlite(adding a backround color in the cell) to the matching numbers in sheet 1.


View Replies!   View Related
Highlight The Cells
I have a spreadsheet that I need a m acro for, that will look at cell C13, if it is a 0 (zero) then hightlight cell C1 yellow. And so on, if C14 is zero, highlight C2..
Also look at the cells below,
cell D13, if it is a 0 (zero) then hightlight cell D1 yellow. And so on, if D14 is zero, highlight D2..


View Replies!   View Related
Not Highlight All The Cells
I have a workbook and trying to copy all the way down the workbook using F4 blanks etc. When I highlight the column I want to fill in its not highlighting all the cells.

View Replies!   View Related
Highlight Cells That Are Above A Given Figure
I am still new to VBA. I am trying to use VBA to search given rows for percentages above 0.81%. When it finds cells with percentages 0.81 and above it will high light the cell with red.

Here is what I have done so far ...

View Replies!   View Related
Highlight Any Dates In Certain Cells
I am trying to create a formula for conditional formatting to highlight any dates in certain cells within a 30 day parameter of a set date .

=AND(A1-DATE(2010,3,1)>=0,A1-DATE(2010,3,1)

View Replies!   View Related
Highlight Cells Using A Button
I am trying to use a button, that when pressed it highlights certain cells in a row

I have the following code

Sub HiLite()
With Range("C20:Q20")
.FormatConditions.Delete
If Range("C20").Interior.ColorIndex = 3 Then
.Interior.ColorIndex = xlNone
Else
.Interior.ColorIndex = 3
End If
End With
End Sub

This works fine, but when i press the button again it clears the highlighted cells back to normal but any highlighted text etc or conditional formatting is deleted

Is there a way of returning to what was there before the first press of the button?

Just incase it is pressed accidently etc, as there will be many rows with the same button

View Replies!   View Related
Cells Wont Highlight
Excel 2003,

I am having an issue with a workbook, when the workbook opens it runs through each sheet and protects it (allowing the user to manipulate unlocked cells). For some reason one sheet will not allow me to do that (in some respect). I can click on a cell, it will show me the formula at the top but the cell wont highlight showing you which one is selected. I have to use the named range box (top left) to see which one is selected.
All the pther sheets work fine. I have checked to make sure that they are not set to Locked (in the cell formatting).

the code that protects the sheets is.......
Sub MyProtect()

For Each ws In Worksheets
ws.Protect
ActiveSheet.EnableSelection = xlUnlockedCells
Next ws

Sheets("Costs").Select
End Sub

View Replies!   View Related
Search And Highlight Cells ..
I have 3 worksheets A, B and C. Worksheet A and B have numbers in three columns. In worksheet C, column D, I would like a formula or conditional formatting which will search worksheet A for the number in column D and if it finds the same number as the one in the cell, it highlights the cell Green. I would also like it to search worksheet B and if the number is in worksheet B, highlight the cell Red. In no cases will the number be in both worksheets (A and B).

View Replies!   View Related
Highlight Cells Containing Certain Month
I would like to highlight the rows from Range A1:A200 that contain the month of October. The dates in this range are written as January 1, 2010, February 5, 2009, etc. I guess I just do not understand how to extract parts of a date. I have part of a formula that works if I place the exact full date in the code but I only want to grab the month so that all the October dates are highlighted.

This is what I have:

Range(Range("A1:A200"), Range("A1").End(xlDown)).Select
For Each cell In Selection
If cell = October Then cell.EntireRow.Interior.ColorIndex = 36 'light yellow
Next cell

I would like to do this in vba and not in conditional formatting.

View Replies!   View Related
Highlight Non Used Mandatory Cells
I have made a spreadsheets wherein all the cells where data are to be entered are coloured yellow.Some cells are mandatory (coloured Green)wherein user have to fill data compulsorily & some are optional (Coloured grey) Since the file is big and sheets are arond 28, I want to attach a buuton which will be assigned a macro to do the validation work.

The macro will search all yellow cells & green cells and if found any of those cell blank will change those colour to red and shall also show a list where the cells are merked red (List will be in a pop up manner, printable, and shall show the name of the sheets & cell address)

View Replies!   View Related
Identify/Highlight When 2 Corresponding Cells Are Different
Am looking for a VBA code that will highlight a changed/edited text from a particular cell. For example - If I have a text in cell B2 as - TEST, and the same text is in C2 as well. However, when I change text in C2 to TEST1 or TES, the change should be highlighted.

View Replies!   View Related
Highlight Unequal Adjacent Cells
I am attempting to program a column of cells so that if say "B3" doesn't match the contents of "A3", then "B3" will be highlighted in some way, maybe with a color. I'm using Excel 2000 and I can't see a way in Conditional Formatting to accomplish this without having to program each cell in column "B". I was hoping to be able to click and drag down in column "B".

View Replies!   View Related
Conditional Formatting : Highlight THE CELLS
I need to hightlight from A1:A18

In the above cells I have values from 1 to 4

So I need to fill the cell with 4 different colours.

If cell equals 1 = Green
If cell equals 2 = Yellow
If cell equals 3 = Red
If cell equals 4 = Blue

What I do is highlight cell from A1:A18, clik on 'Format', 'Conditional Formatting', I've tried using 'Cells Value is' and 'Formula is'.


View Replies!   View Related
Highlight Duplicate Cells Between 2 Columns
I have a spreadsheet with columns A to W populated with data and 2470 rows
Column A has email addresses in it (2470 rows)
Column B has email addresses that are bad and are duplicated of those found in Column A there are only 345 of them in column B.

I would like to compare columns A and B and highlight the email addresses in column A that are also found in column B.

OR: If column A has a duplicate found in column B I would like the word delete to be inserted into column C of that row so I can review and then delete later.

View Replies!   View Related
Highlight The Cells When Criteria Met
=IF(AND($P2="Yes",COUNTIF($L:$L,L2)>1,$Y2="Market Specific"),TRUE,FALSE)
But it is not doing exactly what I want it to, and I am struggling to work out what I need to change to get it to do so.

I want the formula to do is look at a three Columns of data, and to highlight Cells in Column Y when the following criteria are met:

The Row is active (determined by the "Yes" part), that the Row is "Market Specific" and that there is more than one Row where these Criteria are met (this is where I thought COUNTIF would work, but it's not).


View Replies!   View Related
Highlight Cells Meeting Condition
I have a spread sheet with the following data (starts from A1). Please note that a number of rows may not have the ShipmentValue and/or CustCategory:

OrderDate, CustName, ProdName, ShipmentStatus, ShipmentValue, CustCategory

I am trying to write a code which will highlight (say with Yellow Fill) those rows (Columns A:F) Where the ShipmentStatus = "Late" or where ShipmentValue is missing (i.e. blank). I have been able to get only as far as the code below and would appreciate your help in completing the
Sub Highlight()
' This code will highlight those rows A:F in which the Shipment Status
' (Column D) = "Late" or the ShipmentValue (Column E) is blank

Dim MyRng As Range
Dim StatusChk As String
Dim RowCount As Integer ' Counter to count the # of rows
Dim ColCount As Integer ' Counter to count the # of columns

StatusChkCriteria = "Late"

View Replies!   View Related
Highlight A Row Of Cells That Contain Same Date
I am trying to figure out how to highlight the row of cells within the column that contains the same date as the date in cell e:1 the date in cell e:1 changes daily because the formula says =today(). With that said, the column contains every date of the year so i would just like to be able to have the row, that contains the same date as the date in cell e:1, highlighted AUTOMATICALLY.

View Replies!   View Related
Highlight The Cell And All Of The Cells Along The Same Row
Is there a way that when a cell is selected it will highlight the cell and all of the cells along the same row?

View Replies!   View Related
Condtional Formatting: Highlight The Cells
i want cell a1 to highlight if cells b1,c1,d1 are greater than 1.

View Replies!   View Related
Highlight Cells Housing 1 Of X Numbers
I have tried a little bit of different conditional formatting but cannot seem to get what I want.

So then I found this code that will do what I want if I could get it to recognize the highlight.

Sub HilightRows()
For Each cl In Range("C:C")
If cl.Value = 202546 Or cl.Value = 102725 Or cl.Value = 192473 Then
Rows(cl.Row).Highlight = True
End If
Next

End Sub

I want to be able to highlight rows that have certain numbers. When it finds the number it highlights it and goes to the next one and highlights it and so on. The numbers reside in column 'C'.

I changed Row(cl.Row).HiddenRows = True, To Row(cl.Row).Highlight = True. But it is not working.

View Replies!   View Related
Highlight Cells Found In List
I am working for a non profit humanitarian org and I am trying to modify an excel 2003 spreadsheet to change the formatting (color) in one cell based on the value of another cell. Obviously this is easy with conditional formatting, except that I have a list of around 25 unique values and formats.

Could anyone point me in the direction of some code that can do this? I am a relative novice to Excel when it comes to VBA and scripting things.

View Replies!   View Related
Color/Highlight Cells Value That Matches Another
Example:

On sheet "Inventory List" is the value there is a value in cell A2. If that value is found in sheet "FHS Locker List" I would like the box highlighted. (I can do the highlighting) I would like to copy the formula down for cells A2-A900. It would be looking in cells K3-K900 in the "FHS Locker List" sheet.

View Replies!   View Related
Highlight Cells If Duplicate Found On The Same Row
how to even start this macro but i will like the macro to find duplicate with thin the same row and highlight it i have done with conditional formatting but the spreadsheet is about 3000 rows excel performance is super slow if anybody has a macro out there.

View Replies!   View Related
Highlight Cells If Cell Has Dependents Or Precedents
I am working on a tool to highlight cells that have dependents but no precedents. This would be useful for me as I build a number of models and handle models built by others.

I want to be able to easily see where model inputs are and inputs will always be cells that do not have precedents and do have at least one dependent.

I have played with a number of methods with very little success. Is there an easy way to test whether or not a cell has a precedent or dependent within the workbook?

for cell in range

if cell.does.not.have.precedent and cell.has.dependent then
cell.Interior.ColorIndex = 35
end iff

next cell

I found this in an older post but I cant get it to work at all.

Sub atest()

Dim rngA As Range
Dim sngA As Single

For Each rngA In Selection.Cells
If Len(rngA.Formula) > 0 Then
On Error Resume Next
sngA = rngA.DirectDependents.Count
If sngA = 0 Then
rngA.Style = "NoDeps"
End If
End If
Next
End Sub

View Replies!   View Related
Numeric Values From One Sheet To Highlight Cells On Another
I’m trying to assign building and office keys to various personnel, by appointing different classification levels to each set of keys given out. Very similar in the way an administrator would assign permission levels to individuals.

Question:
•Is there a way to pick up an array of non-sequential numbers from one sheet (values 1 -36)
•Find those values which are assigned to columns laid out as a table,
•Then highlight those found values

I’ve attached a dummy sheet to illustrate my question.

I have not attempted coding this because I wouldn’t know where to start.
Theoretically is this even possible?

View Replies!   View Related
Conditional Formatting (highlight The Cells Red )
i am trying to highlight the cells red in the b column if a3=d3 (if names match) and if b3<e3 (only if name is "a" should e3 be used to compare with b3). hope this makes sense. i've attached a sample worksheet.

i've tried =if(b3<e3, (vlookup(a3,d3:e33,1,false),true)...but does not work correctly. i need the average weight to remain constant according to the name when comparing, but if i drag the formula down to other cells, the average weight does not correspond to the name.

View Replies!   View Related
VBA To Copy, Highlight Cells Then E-mail.
I have got the following code that automatically copies a range of cells within the sheet i am on and then creates a new excel workbook then sends it to an e-mail address specified.

Is there anyway i can change it so that it copies the specified range (A1:K50) but then when it pastes that range into another sheet i would like it to highlight some of the cells yellow based on what i had higlighted on the sheet when i run the vba. i hightlight B6:E10 then run the VBA, i want it to copy A1:K50 into another sheet (as below) but then on the new sheet highlight cells B6:E10.

View Replies!   View Related
Vlookup Macro: Highlight The Cells In The In The Workbook
I have created a macro to open a workbook from another workbook and do a vlookup against the data in the source workbook. I then wanted to highlight the cells in the in the workbook that I'm doing the vlookup from, and to unhighlight any empty cells. I did this all without code using the macro recorder so that I could then study the code and learn something.

View Replies!   View Related
If Two Cells Match (date), Highlight A Third Cell...
I have a row D2:ND2 where each cell contains a date from October 1st through to September 20th). I also have a column NI3:NI13 that contains various dates such as Easter or bank holidays. Would it be possible to use conditional formatting (or something else) to highlight all rows below D2:ND2 if the dates specified in each cell in D2:ND2 match any of the dates in column NI3:NI13?

View Replies!   View Related
Conditional Formatting To Highlight Cells With Formulas
I have searched for a while but can't seem to find where the original thread is at. Could someone point me in the right direction?

View Replies!   View Related
Highlight Rows If Cells Contain Specific Values
I need to creating a macro to highlight entire rows if a cell in that row (C) contains specific values.

View Replies!   View Related
Automatically Highlight Cells That Have Text Or Number Below X
How can I use conditional formating to produce the following:

if cells are between 0 and 10 OR they have text(or an error) = red
if cells >10<25 = orange
if cells > 25 = green

View Replies!   View Related
Highlight Cell If Surrounding Cells Close To Value
how can I create a formulae that

Highlight a Cell IF

Its surrounding cells are

+1 or +2
-1 or -2

away from that cell value.

Attached a file for example.

View Replies!   View Related
Highlight Used Cells For Pivot Report Selection
Below is my code to create a pivot table. The data is located on a 2nd sheet named 'Data2' in columns B & C. As you can see the range is already defined. How can i have this as a variable which holds the current address of the automatically selected - occupied cells in columns b-c? So in order a macro that can.

1) select all occupied cells in colums b-c
2)paste this range as an address in d1
3) assign a variable which has the range for the pivot table macro to complete its work.

Sub Macro8()
Sheets("PivotReport").Select
Range("B18").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Data2!R1C2:R27C3").CreatePivotTable TableDestination:= _
"'PivotReport'!R18C2", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Products")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("QTY"), "Sum of QTY", xlSum
ActiveWorkbook.ShowPivotTableFieldList = False
Application. CommandBars("PivotTable").Visible = False
End Sub

View Replies!   View Related
Highlight Cells Matching Selected Cell
I would like to select a cell inside my workbook and when it is selected it automatically highlights similar value cells in the same workbook. Is that possible? A Look at the attachment should give you a much better idea as to what I am talking about.

View Replies!   View Related
Highlight/select Data Between 2 Cells Containing Specific Words
Sample data (all in column a):

Heading 1
123
12345
1234
345
Heading 2
987
9876
Heading 3
785
Heading 1
2356
8525
Heading 2.........

View Replies!   View Related
Conditional Formatting :: A1 Is Equal To Data Highlight Cells On A2
how to create a conditional formatting formula that looks like this..

"If A1 is equal to DATA highlights greater than zero on cells A2 to A1000.

View Replies!   View Related
Highlight Cells Based On Row/Column Being Visible
I am attempting to have a cell highlighted if the adjacent cells are visible, but no highlighting when the cells are hidden. For example, Cell D1 is highlighted when cells A1:C1 are visible, but not when cells A1:C1 are hidden. (this is for the group/outline feature)

View Replies!   View Related
Highlight & Move Cells Based On Criteria
I have 3 data series, which are each futures contracts. Each of these futures contracts (the one on the left expires soonest) has an expiration date and as that date approaches, one will need to replace it with a new futures contract (the data series immediately to its right) having a later expiration date. This process is called a ‘roll’. I am seeking to create a continuous data series, which will incorporate the appropriate ‘roll’ based on two rules.
The rules are as follows:

1) Where the volume of the 2nd contract exceeds the volume of the 1st contract for 10 consecutive intervals, roll on that date.
2) Once the roll date has been identified, roll at the specific time interval on that date that has the highest aggregate volume (between the 1st and 2nd contract).
I have laid out the original data in a tab entitled ‘original data stream’. This is how the data will appear at first. In the tab entitled ‘Roll Example’, I have shown what I would like to happen to the original data streams. You will notice that in this tab, the data series are the same as the previous tab, except that they are not aligned as before. The data series to the left ( cells B4: D808) expires ..................................

View Replies!   View Related
Compare Cells In Different Workbooks & Highlight Differences
I am trying to find a way to compare the cells (example: D4:D12 to the same cell range in another workbook. If the value in D4 is less than D4 in workbook 2 highlight it red and if it is greater highlight it green. I also want a loop to go through the specified range. Of course, the scale of the worksheets is much greater.
Another issue I foresee, is that the workbook name that I am comparing to changes every week, so is there a way to handle this change easily?

View Replies!   View Related
Auto-populate Cells With Data From Other Cells
My sheet goes from monday to friday on the coulombs. On the rows i have various data sections for each day. I would like the data on the last day (any day after monday) that there is data for a sheet to input that data into the monday slot when the "master date" is changed on the cheese sheet. The idea here being that the script will take the last entered data for a given sheet, and put it into the monday coulomb on the same sheet when the date is changed. This is kind of hard to explain so if you need clarification let me know. Attached is the sheet i'm working with.

View Replies!   View Related
Keyboard Shortcut To Highlight All Cells In A Column From Bottom To Top
What is the best way to highlight all cells in a column from bottom to top?

For example, if I'm in column O, row 138, what keyboard shortcut would I need to select ONLY and ALL cells from the row I'm currently on, (in this example, 138) to row 1 and ONLY in that particular column?

Keep in mind that the next time I may be starting at row 1,200, it could be different everytime.

I tried everything I could think of..


CTRL+A
CTRL+SHIFT+A
CTRL+HOME
CTRL+SHIFT+HOME
CTRL+UP ARROW
CTRL+SHIFT+UP ARROW
CTRL+PAGE UP
CTRL+SHIFT+PAGE UP

and nothing worked to select everything in that column from the row I'm currently on, to row 1.

View Replies!   View Related
Conditional Formating: Highlight Cells Based On Multiple Stipulations
I'm trying to get it so that a cell becomes automatically highlighted if 2 other cells conditions are met. For example, I want cell A5 to become highlighted if BOTH cell B1=Loan 1 AND cell B5=Yes. Here is a mockup of what I'm talking about incase it makes it easier to understand. I'm using Excel 2007.

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved