Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting adjacent cells
Hi,
I'm trying to conditional format adjacent cells on my worksheet, For example please see table below: Emp No Name DOB Dept Grade Decision 12345 Rachel 28/02/78 HR A Yes 45678 Ian 15/10/71 Head Office B Yes 55678 James 15/11/81 Finance C No 55678 John 11/09/78 Finance A Yes 48567 Matt 20/09/56 HR B No I somehow need to check what is present in the decision column (F) and depending on whether the result is Yes or No colour format the rest of the row of data. For example I want all Yes to be red and all No's to be green. When i attempt to format in code the cell with the Yes/No in it is the only cell that gets formatted - how can i format the whole row depending on the result in column F (decision). Also, How do i select one row after another and how can you tell the computer when it has reached a row with no text present. Any help would be greatly appreciated. R Curran |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting adjacent cells
Do you want to do this with code? Because you could just
do this using the Format|Conditional Formatting menu option Rgds Rog -----Original Message----- Hi, I'm trying to conditional format adjacent cells on my worksheet, For example please see table below: Emp No Name DOB Dept Grade Decision 12345 Rachel 28/02/78 HR A Yes 45678 Ian 15/10/71 Head Office B Yes 55678 James 15/11/81 Finance C No 55678 John 11/09/78 Finance A Yes 48567 Matt 20/09/56 HR B No I somehow need to check what is present in the decision column (F) and depending on whether the result is Yes or No colour format the rest of the row of data. For example I want all Yes to be red and all No's to be green. When i attempt to format in code the cell with the Yes/No in it is the only cell that gets formatted - how can i format the whole row depending on the result in column F (decision). Also, How do i select one row after another and how can you tell the computer when it has reached a row with no text present. Any help would be greatly appreciated. R Curran . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting adjacent cells
Hi Rachel,
You do the conditional formatting to the entire row based on a specific column the same as you do when you test a column. Just make sure that your column has absolute reference for the column portions. The cells that get colored are based on the selection, the formula that you use is based on the active cell. I would only use formulas myself and not the "simplified" comparisons. More information on Conditional Formatting with examples in http://www.mvps.org/dmcritchie/excel/condfmt.htm Select cell F1 Ctrl+A to select all cell on the worksheet, F1 remains the active cell Format, Conditional Formating Formula 1: =F1="Yes" format with a pastel blue pattern I think that would be enough color for Yes/No or blank, but if you want Formula 2: =F1="No" format with a pastel yellow pattern Don't worry about the empty cells, if you didn't include them in your test you won't be adding color. Conditional formatting has to get a True condition or a False condition. Errors such as text in a numeric test are treated as False so you don't need to include a lot of additional tests as when you are trying to calculate a value. If you wanted to show empty cells as an incomplete condition C.F. formula 3 is: =TRIM(D3}="" or C.F. formula 3 is: =AND(TRIM(D3)="", A3<="") ------ As another example your data could use Conditional Formatting like this: Select cell D1 Ctrl+A to select all cell on the worksheet, F1 remains the active cell Format, Conditional Formatting Formula 1: =D1="HR" There is a limit of 3 formulas per cell, for C.F. since you are involving the entire row, that means that every cell in the selected rows is included in the test, in other words each cell in the rows is tested. Since you are limited to 3, you might want to combine some. Formula 1: =OR(D1="HR", D1="Head Office") Formula 2: =D1="Sales" Formula 3: =D1="Facilities" If you need more than three you would have to use an Event Macro http://www.mvps.org/dmcritchie/excel/event.htm#case You might also consider Data, Filter, .... to show only the rows you are interested in. Debra Dalgleish has pages on Filtering. Much of what you use in Conditional Formatting applies to Filtering as well. The drop down list of unique items was increased in Excel 97 and Excel 98 to 1000 from 250. [MSKB 105322]. Excel Filters -- Advanced Filter, Debra Dalgleish. http://www.contextures.com/xladvfilter01.html#ExtractWs Additional links on my sumdata.htm#related HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm From: "Rog" Do you want to do this with code? Because you could just do this using the Format|Conditional Formatting menu option "Rachel Curran" wrote in message I'm trying to conditional format adjacent cells on my worksheet, I somehow need to check what is present in the decision column (F) and depending on whether the result is Yes or No colour format the entire row Emp No Name DOB Dept Grade Decision 12345 Rachel 28/02/78 HR A Yes 45678 Ian 15/10/71 Head Office B Yes 55678 James 15/11/81 Finance C No 55678 John 11/09/78 Finance A Yes 48567 Matt 20/09/56 HR B No |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting adjacent cells
"Rog" wrote in message ...
Do you want to do this with code? Because you could just do this using the Format|Conditional Formatting menu option Rgds Rog -----Original Message----- Hi, I'm trying to conditional format adjacent cells on my worksheet, For example please see table below: Emp No Name DOB Dept Grade Decision 12345 Rachel 28/02/78 HR A Yes 45678 Ian 15/10/71 Head Office B Yes 55678 James 15/11/81 Finance C No 55678 John 11/09/78 Finance A Yes 48567 Matt 20/09/56 HR B No I somehow need to check what is present in the decision column (F) and depending on whether the result is Yes or No colour format the rest of the row of data. For example I want all Yes to be red and all No's to be green. When i attempt to format in code the cell with the Yes/No in it is the only cell that gets formatted - how can i format the whole row depending on the result in column F (decision). Also, How do i select one row after another and how can you tell the computer when it has reached a row with no text present. Any help would be greatly appreciated. R Curran . Yes, I need this to be in code please |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting adjacent cells
Hi Rachel,
See my previous response in this thread, but it sounds like you are still looking for an answer; otherwise you would have said you had your answer. This example will change the color of the entire row based on the value manually entered into Column F (column 6) having "Yes" or "No" with an Event Macro instead of using Conditional Formatting. . VBA is case sensitive whereas the worksheet formulas used in Conditional Formatting were not, so the testing will be of the value as converted to lowercase. Read again about Event Macros in http://www.mvps.org/dmcritchie/excel/event.htm#case To install right click on the sheet tab then View code, insert code Private Sub Worksheet_Change(ByVal Target As Range) 'David McRitchie, 2004-09-26, programming, Case -- Entire Row ' http://www.mvps.org/dmcritchie/excel/event.htm#case_row If Target.Column < 6 Then Exit Sub 'Column F is column 6 If Target.Row = 1 Then Exit Sub Application.EnableEvents = False 'should be part of Change macro Select Case LCase(Target.Value) Case "yes" Target.EntireRow.Interior.ColorIndex = 34 Case "no" Target.EntireRow.Interior.ColorIndex = 36 Case Else Target.EntireRow.Interior.ColorIndex = xlColorIndexAutomatic End Select Application.EnableEvents = True 'should be part of Change macro End Sub --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Rachel Curran" wrote in message Yes, I need this to be in code please |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional formatting adjacent cells | Excel Worksheet Functions | |||
Conditional Formatting Relative to adjacent cells | Excel Discussion (Misc queries) | |||
Conditional formatting on ADJACENT Cells | Excel Discussion (Misc queries) | |||
Conditional Formatting for adjacent cells | Excel Discussion (Misc queries) | |||
conditional formatting adjacent cells | Excel Worksheet Functions |