Highlight Row / Column With Conditional Formatting


Search Excel Forum Posts, Tutorials, Macros, Tips, and More
Highlight Row / Column With Conditional Formatting  Excel

View Answers


I have a workbook saved as Excel 97/2003 workbook*.xls (compatibility mode) that uses the following VBA and conditional formatting to highlight the row/column the selected cell occupies.
VBA
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) Application.ScreenUpdating = True
Conditional Formatting formula
=OR(AND(CELL("row")=ROW(),COLUMN()
Similar Excel Video Tutorials
Conditional Formatting 12 Examples
 See how to add conditional formatting for:
1)Data Bars to show relative size
2)Icons to show relative size
3)Highlight values within 1 ...
Conditional Formatting Max In Row
 See how to apply a conditional format that will highlight the maximum value in a row using a True False Formula. See the MAX function used with Condit ...
Similar Topics
This VBA & conditional formatting to highlight row & column worked just fine in Excel 2003, there seem to be slight differences in 2007 that I cannot unravel. Can anyone show the modification to make this work in 2007
select MS Excel Objects
double click ThisWorkbook
left pull down menu  select workbook
right pull down  SheetSelectionChange
Add "Application.ScreenUpdating=true
VBA FORMULA
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Application.ScreenUpdating = True
End Sub
CONDITIONAL FORMATTING FORMULA
FORMULA IS:
=OR(AND(CELL("row")=ROW(),COLUMN()
I have used a VBA with cond format in Excel 2003 in this way:
VBA:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) Application.ScreenUpdating = True
Conditional Format:
=OR(AND(CELL("row")=ROW(),COLUMN()
Hi guys
Im using a formula with conditional formatting and a change event code to highlight a selection of a row when a cell within that row is selected.
Everything works fine, however can I get this to only highlight the selection when the active cell is within a range.
The range I want is L10:P45, so if the active cell is within that range, the formating kicks in but if it isn't in that range then no formatting occurs.
Could I also have the formating to be applied if I only hover over a cell instead of clicking on the cell?
This is my formula for the conditional formatting:
=CELL("row")=ROW()
The event code:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Application.ScreenUpdating = True
End Sub
Thanks

This VBA & conditional formatting highlights the row & column of the cell you select in the sheet, quite useful when dealing with a lot of columns & rows.
I recognize the symptoms of my problem, in Excel 2003 if your VBA was messed up, the conditional formatting worked but you had to leave the worksheet and return for the formatting to update, this is what it is doing now but I cannot get the VBA and/or conditional formattine to cooperate in Excel
Steps I used to insert VBA:
Press Alt F11
locate file
select MS Excel Objects
double click ThisWorkbook
left pull down menu  select workbook
right pull down  SheetSelectionChange
Add "Application.ScreenUpdating=true
VBA FORMULA SHOULD LOOK LIKE THIS
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Application.ScreenUpdating = True
End Sub
Conditional Formatting steps I used:
SELECT WHOLE WORKSHEET FOR THIS CONDITIONAL FORMATTING FORMULA
FORMULA IS:
=OR(AND(CELL("row")=ROW(),COLUMN()<=CELL("col")),AND(CELL("col")=COLUMN(),ROW()<=CELL("row")))
Can anyone tell me why this will work in Excel 2003 and not in Excel 2007. I have repeatedly checked accuracy in both versions, no joy in the 2007 version.
Thanks in advance,
Mark
In Excel 2003, I'm trying to autohide rows when the value in any cell of a certain column is "Closed" This can't be done via conditional formatting. Any other ideas (VBA code)?
I found below code excerpt but it did not work for me. Maybe it's not specifying what cells it should look in... or I'm not doing something correctly.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Value = "Closed" Then
Rows(Target.Row).RowHeight = 0
Else
End If
End Sub
Is there a way to have VBA highlight a cell yellow respective to what column the active cell is in?
Let me explain what I am thinking here. I have a spreadsheet and its long and dreadful with small text. I want to create VBA code on the workbook: Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range). This way when users are inserting numbers into the spreadsheet, it will highlight and increase the size of cell A#. So if they are in cell H13 and inserting numbers I want A13 to be yellow with large text. Then if the user moves to cell M56, A13 goes back to regular text and regular background and cell A56 will highlight yellow and become bigger text size. This way they can easily see which client they are updating. I am hoping this code wont slow down the worksheet TOO much. Any ideas??
Maybe I could do this with conditional formatting???
I have a range that is about 40 rows. At the end of each column is a total. I have about 30 columns of data all with different totals at the end. I have set up Conditional Formatting that if the range contains a number that is greater than or equal to that column total highlight in red and Bold.
I am looking for an easy way to copy that Conditional Formatting to all the columns instead of having to highlight each column and enter the same Conditional Formatting criteria. I have tried the format painter and Paste Special > Formats and nothing works.
Anyone have any ideas on copying Conditional Formatting to other cellls?
Much Appreciated!
I'm trying to highlight 2 cells when a particular name is in a column using conditional formatting. I have to use VBA code as I already have 3 other conditional statements. The code works other than when the name is removed or change, the color formatting remains. I want it to only be there when this text(name) is in the column. Here is my coding:
Private Sub Worksheet_Change(ByVal Target As Range)
Set MyPlage = Range("A1:T250")
For Each Cell In MyPlage
If Cell.Value = "Ken Ballard" Then
Range("A" + CStr(Cell.Row) + ":B" + CStr(Cell.Row)).Interior.ColorIndex = 43
End If
Next
End Sub
Any help would be appreciated! Thanks!
I've tried playing with Conditional Formatting but for some reason can't make it do what I need!
I have a large spreadsheet with 93 columns, and I need some way to say:
IF Cell AA3 has a value of Y  Highlight BW3:CB3, and so on for each cell down (if AA4 = Y Highlight BW4:CB4 etc.)
We need to highlight them because if the answer to heading AA is Y then BW:CB is mandatory...
I tried putting a formula in conditional formatting, custom... but for some reason it didn't do anything
I have a code here that will highlight the entire row when one of the cell is selected.
Any one can modify this to just the first cell of the selected row? Thanks a lot!
Here's the code:
VB:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Static OldRng As Range
On Error Resume Next
Target.EntireRow.Interior.ColorIndex = 6
OldRng.EntireRow.Interior.ColorIndex = xlColorIndexNone
Set OldRng = Target
End Sub
If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines
I have a hundreds of records that came to me in a pdf file. By highlighting the rows of data and right clicking in Adobe Acrobat I have the option of opening the highlighted data in a spreadsheet. I've done that and then saved as an XLS file in Excel 2002.
I have a column of time records. The data looks like this: " 7:27 PM". When I click on the column and select "format", "cells", the default is General. My goal is to use conditional formatting to highlight data items that fall in a certain range. I use the following commands in conditional formatting:
Cell Value is between =time(6,0,0) and =time(6,59,59) to highlight times in the 6:00 am hour.
What can I do to manipulate the data to get it in a format where I can apply the conditional formatting? Failing that, is there a way to apply a similar conditional formatting (or other method) on the data as I've received it?
Thanks in advance!
Hello
I have a large excel spreadsheet (over 1000 rows & 50 columns).
I want to do some conditional formatting.
For each row, I wish to highlight some (not all) of the cells in that row depending on the value in a particular column.
For example:
I wish to highlight the first 25 cells in the row, if and only if the value in column Z of that row is equal to 100%.
I can do this on a row by row basis by using conditional formating, by highlighting the cells in the particular row I wish to format (shading them grey) selecting the Formula Is in the conditional formatting drop down box and typing =$Z$5, and hey presto when cell Z in row 5 is 100% it highlights the required cells in that row grey.
I could do this same conditional formatting for each row, but with over 1000 rows this would take forever. I'm trying to determine if there is a quicker way to do this for all the rows at the same time.
Please help.
Thanks
Mark
Hey board,
I highlighted a column and selected "conditional formatting > highlight cell rules > less than" then entered in a date and selected formatting (red text). This did not work. My goal is to have any date less than 06/10/2010 highlight red in my column. How may I achieve this?
I'm on Windows XP and Excel 2007.
I need the cells in a column (Range) to highlight if they are duplicate numbers only and ignore any text. I tried =countif(A2:A10,A2)>1 and then formatted it to highlight red just to see it highlight duplicates and all it did was highlight every cell within that range red. So what formula would I use in conditional formatting to highlight only numeric duplicates?
Thanks,
Jay
Hi There,
I have a question regarding a formula for conditional formatting. I would like a formula to conditional formate an entire row if it is exactly the same as another row. Both rows should be highlighted.
Each row is a transaction that is being recorded manually, thus the duplication.
I am looking to do this without adding an additional column to the database, as I am currently doing it.
This is how I am currently approaching it.
An additional column that is a concatenation of field in a row. i.e. =concatenate(A2,B2,C2,D2,E2,F2,G2). Then I am able to use the following formula in conditional formatting to highlight the duplicated rows.
=IF(COUNTIF($H:$H,$H2)>1,TRUE,FALSE)).
I tried an array within the conditional formatting, but had no luck.
I am using Excel 2010.
Kindest Regards,
Mark Blackburn
Dear Sir,
I tried to use the Get.Cell BUT the Excel 2003 does not seem to know it exist.
I tried the above example from your book Excel Gurus Gone Wild but they do not work.
To highlight every cell that does not contain a formula , use =NOT(hasFormula) in the conditional formatting.
1. First I go ti define Name
2. Enter Names in Workbook HasFormula
3. Then go to Refers to : and entered =GET.CELL(48,INDIRECRT("RC",FALSE))
4. Then I select a range of sells
5. Select Format, Conditional Formatting, Change the first dropdown to Formula is Type = HASFORMULA and then click OK
5. Then I tried the formula =NOT(hasFormula) in the conditional formatting but it does not work ?
Why ?
I am in dire need of assistance.
I have attached an simplified version of the spreadsheet I am using but have removed all conditional formatting that I have been using.
Now, what I want to do it evaluate each row and if the last value for that row is X, then highlight that cell and the Server name for that row.
I have used conditional formatting but it seems that I cannot specify an option to look at the last cell in a row, evaluate that cell's value and then apply the formatting. I can only have conditional formatting highlight every cell in that row that matches the condition.
I only want the last cell evaluated, which represents the most current data, to be highlighted, as well as the category name, if the condition is met.
Anybody know of a way to accomplish that? All suggestions welcome and thank you in advance.
I'm crap with conditional formatting, so I just need to know how to do this if it is possible. I've got some code that will achieve my goal, but I would prefer to use a nice builtin function like Conditional Formatting if possible.
I'll just use one row as an example.
I have two dates. e.g. one is housed in Column A and one in column B. If column B date is later than column A then I want to apply formatting to the cells in A1:G1.
Can I do this through conditional formatting? Or is Conditional Formatting limited to comparing the contents of the cell with the formatting to another cell?
Thanks
Hi there,
I am trying to create a conditional formatting in Excel 2003 in a single column to locate and highlight the values/formulae that is different than the others in that very column. For example: Column D summs all values in range A:C as below:
D1 = SUM(A1:C1)
D2 = (SUM(A2:C2)+10)
D3 = SUM(A3:C3)
D4 = (SUM(A4:C4)5)
D5 = SUM(A5:C5)
D6 = SUM(A6:C6)
However, occasionally I might manually insert additional numbers like in cells D2 & D4 above.
What I want from conditional formatting is to locate such inconsistent cells like D2 and D4 and highlight them.
Tip: I can find such cells by pressing F5>>>Specials>>>Column Differences, but this is not handy and it's timewasting
Appreciate any tip
Regards,
MrExceller
Hi folks having a problem with conditional formatting I have a database columns A to K. What I want to do is format a cell in column A if the corresponding cell in column K says "Sch"
I highlight cells a2:a300
I open the conditional formatting box with is formula= $k2$:$k300$="sch"
but it doesn't work can anyone help and tell me what I am doing wrong
Cheers
Hi, I have just got a new laptop with Excel 2007 on it, and I am really struggling to adapt to it. I am trying to do some conditional formatting but having difficulty:
I have a sheet with all of my sales for the previous 12 months in columns BM. Each row represents a different customer. So for example, in row 1  A1=customer name B1:M1=units bought per month for the last 12 months.
There is a conditional formatting wizard in excel 2007 which allows you to insert a small dot / arrow in the cell. If the value is =67% is green.
If I highlight cells B1:M1 and hit Alt, H, L, I , then Excel applies this conditional formatting to the row. I now want to apply the same conditional formatting to all of the rows (1000+), without having to highlight every row individually. If I highlight the entire range (B1:M1000) before applying the formatting, Excel takes an average of the entire range (and I want the icons to represent how the value compares to the average of that particular row).
So after that rather long winded explanation, my question is: Is there an easy way to apply the same conditional formatting to a whole range of cells  by dragging somehow?
Many thanks in advance for your help,
Rich
O P
12M
TRUE
12M
TRUE
yM
FALSE
I have written a macro that inserts a column to the right of column O (the new P), and enters a formula which says if the contents of column O are TRUE or FALSE. I am now trying to write a formula in conditional formatting / code so that if P = FALSE, it will highlight 1 (i.e. 1 column to the right). So having checked if TRUE/FALSE, it will highlight all false values and I can delete column P. Have been fiddling in conditional formatting or writing in VBA but no luck so far... any help would be appreciated, thanks
I have the following in my worksheet:
F G H
=================
L L W
H H W
L L W
L L W
H L L
My conditional formatting will work on the highlighted cell, but I want to highlight 4th 'w' in the column.
This code is in the cell: =IF(G25=$F25,"W","L")
The conditional formatting is to highlight the cell in yellow.
The formatting is;
=AND($H28=$H27, $H27=$H26, $H26=$H25, OR($H28="W", $H28="L"))
I have tried to use OFFSET, but all it does is negate my formatting.
Is the fact that there is a formula in the cell a problem?
I am in dire need of assistance.
I have attached an simplified version of the spreadsheet I am using but have removed all conditional formatting that I have been using.
Now, what I want to do it evaluate each row and if the last value for that row is X, then highlight that cell and the Server name for that row.
I have used conditional formatting but it seems that I cannot specify an option to look at the last cell in a row, evaluate that cell's value and then apply the formatting. I can only have conditional formatting highlight every cell in that row that matches the condition.
I only want the last cell, which represents the most current data, to be highlighted, as well as the category name, if the condition is met.
Anybody know of a way to accomplish that? All suggestions welcome and thank you in advance.
I have a large spreadsheet with matching columns like this:
name_a, type_a, task_a, ... name_b, type_b, ...
I want to highlight all the cells in the "b" columns that don't equal the value in the corresponding cell in the correct "a" column.
I was able to set up conditional formatting on a cell (i.e. if Q3=E3, then highlight Q3), but when I try to apply this formatting to the rest of the Q column, it compares all cells in the column to E3, and I want Q4=E4, Q5=E5, etc.
Is there a way to apply conditional formatting to a column in this way? Thanks!