Close Window   
Free Ebook
Got an Excel Question?
Ask it in our Excel forum!
TeachExcel.com
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



Highlight Row / Column With Conditional Formatting

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

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()
View Answers     

Similar Excel Tutorials

Highlight Rows that Meet a Certain Condition in Excel
In this tutorial I am going to cover how to highlight rows that meet a certain condition. To do this I use the Cond ...
List all Conditional Formatting Formulas in Excel
List all conditional formatting formulas in a worksheet in Excel. This allows you to quickly view and manage all of ...
Highlight and Sort the Top and Bottom Performers in a List in Excel
How to highlight the rows of the top and bottom performers in a list of data. This allows you to quickly identify ...
Display Comments (without hunting for them) - Excel 2003
To find options, go to the File menu, then click options, then click advanced and click on comments and indications ...

Helpful Excel Macros

Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Highlight the Column of the Selected Cell
- This macro will highlight the column of the active cell. This will fill the column of the active cell with a solid back
Highlight the Row and Column of the Selected Cell
- This macro will highlight the row and column of the active cell. This will fill the column and row of the active cell w
Highlight the Row of the Selected Cell
- This macro will highlight the row and column of the active cell. This will fill the row of the active cell with a solid
Highlight Cells which Contain Formulas
- This macro will highlight all of the cells in a worksheet which contain a formula. The first one listed will highlight

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


In Excel 2003, I'm trying to auto-hide 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'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 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'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




My workbook is attached.

Under the 'Current Contractors (Melvindale)' sheet, I am looking at column F. I have conditional formatting to highlight the cell in yellow if it is 4 days before the due date, and Red if it is the day of the due date or past the due date. It is currently only selecting the cell in the F column, but I want it to select the entire row that that cell belongs to. I have never used conditional formatting, and the current formula that I am using, I retrieved from this website: http://www.techonthenet.com/excel/qu...rmat4_2010.php

Please Help

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




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 created a 10x10 array that serves as a multiplication table. I wanted to use conditional formatting to easily highlight all the perfect squares (e.g. 4,16,25,36...)

Is there anyway I can do this by entering in a formula into conditional formatting? I have a basic idea of how I could do this-- =B2=POWER(SQRT(B2),2)... I am confused on how I could do this in conditional formatting formulas.

Any ideas?

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


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 built-in 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, 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 B-M. 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




Hello,

I am not able to use conditional formatting when the condition cell contains ">=" operators.

The sheet has a target column (col A) which has value >=10%
If achievement column (col B) has values:-
<10% it should highlight in Red (e.g.: -10%, 0%, 5%)
if >=10% it should highlight in Green (e.g.: 10%, 20%)

Conditional formatting works on the achievement column when I input 10% in the target column but does not work when i input target as >=10%

I have attached sample excel sheet for your reference, please see Row 6.

Thanks

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.


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


Following is my excel sheet:
A B C D
252 121 39 - 18 70 1 3
487 232 64 - 29 153 5 4
824 413 97 3 103 178 16 14
#N/A #N/A #N/A #N/A
591 269 48 - 63 191 9 11

These are all vlookup functions. I am trying to apply conditional formatting here which will highlight the maximum value. I did the following to apply conditional formatting:

Format>Conditional Formating,
Cell is - equal to - =Max($A$1:$D$9),
Format with GREY color.

But the conditional formatting did not highlight anything. I am guessing this is because I have Vlookup error "#N/A" in some of my cells.

Does anyone has idea how to solve this problem?

Thank you very much.




I have a really basic conditional formatting formula, but can't get it to work after watching numerous youtube tutorials. Trying to using conditional formatting to fill cells in a column red if they have greater than or equal to 10% as their cell value. I highlight all the cell this should affect. Then choose conditional formatting with formula. Type =>=10%, select fill color, click okay and okay. I get an error message "the formula you typed contains an error." If I choose the "highlight cells grearter than" instead of use formula, the action shade every selected cell, even those without numbers.

Help. Been working on this for more than 1 hour. It shouldn't be this hard.

Hi all,

I wonder if someone can help........

I am trying to apply conditional formatting to cells in a column. I want to highlight any cells which show a greater than 10% drop from the previous value, in the cell above.

For example: if D3 is less than 0.9 times the value of D2, then highlight the cell in red. Similarly highlight the cell when D4 is less than 0.9 times the value of D3, D5 is less than 0.9 times the value of D4, etc, etc. I have attached a section of my spreadsheet to demonstrate.

I can set the formatting for the first cell with {cell value<"0.9*$D$2"} and this highlights the cell as required.

However, when I apply the formatting to $D$3:$D$14 (in the conditional formatting rules manager) it compares each cell value to the value in D2, rather than to the cell above. So I tried deleting the dollar signs to give {cell value<"0.9*D2"}. This still compared all cells to D2. I tried using {cell value<"0.9*R[-1]C"}, but this returned an invalid formula error.

At wits end, any suggestions gratefully received.
Thanks


I am trying to set up the conditional formatting in a spread sheet to highlight / format an entire row depending on the information found in cell. EX: highlight the entire Row B once shade if cell B10 = Accepted and another shade if B10 = Rejected. I am only acquainted in the most basic sense with conditional formatting and formulas and even less so in Excel 07 and 10.