Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Color selected row cells?
I hv the following table:
A B C D XT A1 C3 A2 23 C3 C3 A2 13 A2 A1 C3 XT C3 A2 C3 Is there a formula to check column A for the specific text "XT", and then color the row (A-D) with the color of choice? What happens is that the row is incrementally updated with data every week, hence whenever a new row has column A with the text "XT", it should automatically convert row cells of A-D (where "XT" resides) into a color of choice. Possible? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Color selected row cells?
Use conditional formating (menu Format - conditional formating)
If the first ccell is A1 then in conditional formating chage "Cell Value Is" to "formula Is) 1) enter the formula below =($A1="XT") 2) Set format Pattern (conditional formating window) to the color you want. 3) copy cell A1 then select all the cells in your table and PasteSpecial using FORMAT which will copy only the conditional format to all the cells. "andrew" wrote: I hv the following table: A B C D XT A1 C3 A2 23 C3 C3 A2 13 A2 A1 C3 XT C3 A2 C3 Is there a formula to check column A for the specific text "XT", and then color the row (A-D) with the color of choice? What happens is that the row is incrementally updated with data every week, hence whenever a new row has column A with the text "XT", it should automatically convert row cells of A-D (where "XT" resides) into a color of choice. Possible? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Color selected row cells?
Hi Joel, what if the first cell is not with the text "XT"? Does it need a
cell to refer to? See below on my actual table: A B C D E 1/1 13 A1 C3 A2 8/1 23 C3 C3 A2 15/1 13 A2 A1 C3 22/1 XT C3 A2 C3 Column A shows the date. Column B is where i would want the formula to be 'activated' only when the text "XT" appears. When it does (in this case, B4), it will then select row 4 from A-D and highlighted it with green (color). If there's no "XT" appearing in the column then all is normal. In my actual table, row 1-5 has a different data layout, then row 6 onwards is the table you see above. Should i use UDF instead? "Joel" wrote: Use conditional formating (menu Format - conditional formating) If the first ccell is A1 then in conditional formating chage "Cell Value Is" to "formula Is) 1) enter the formula below =($A1="XT") 2) Set format Pattern (conditional formating window) to the color you want. 3) copy cell A1 then select all the cells in your table and PasteSpecial using FORMAT which will copy only the conditional format to all the cells. "andrew" wrote: I hv the following table: A B C D XT A1 C3 A2 23 C3 C3 A2 13 A2 A1 C3 XT C3 A2 C3 Is there a formula to check column A for the specific text "XT", and then color the row (A-D) with the color of choice? What happens is that the row is incrementally updated with data every week, hence whenever a new row has column A with the text "XT", it should automatically convert row cells of A-D (where "XT" resides) into a color of choice. Possible? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Color selected row cells?
Additional question to below. Assuming if i have more than 3 different text
to monitor (each with its own color to fill) is it still possible using Conditional Formatting? This is because the table i'm working with (per below) has up to 5 different "text" to monitor in the same worksheet and highlight selected cells in the row assuming if there is a match. "andrew" wrote: Hi Joel, what if the first cell is not with the text "XT"? Does it need a cell to refer to? See below on my actual table: A B C D E 1/1 13 A1 C3 A2 8/1 23 C3 C3 A2 15/1 13 A2 A1 C3 22/1 XT C3 A2 C3 Column A shows the date. Column B is where i would want the formula to be 'activated' only when the text "XT" appears. When it does (in this case, B4), it will then select row 4 from A-D and highlighted it with green (color). If there's no "XT" appearing in the column then all is normal. In my actual table, row 1-5 has a different data layout, then row 6 onwards is the table you see above. Should i use UDF instead? "Joel" wrote: Use conditional formating (menu Format - conditional formating) If the first ccell is A1 then in conditional formating chage "Cell Value Is" to "formula Is) 1) enter the formula below =($A1="XT") 2) Set format Pattern (conditional formating window) to the color you want. 3) copy cell A1 then select all the cells in your table and PasteSpecial using FORMAT which will copy only the conditional format to all the cells. "andrew" wrote: I hv the following table: A B C D XT A1 C3 A2 23 C3 C3 A2 13 A2 A1 C3 XT C3 A2 C3 Is there a formula to check column A for the specific text "XT", and then color the row (A-D) with the color of choice? What happens is that the row is incrementally updated with data every week, hence whenever a new row has column A with the text "XT", it should automatically convert row cells of A-D (where "XT" resides) into a color of choice. Possible? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Color selected row cells?
Conditional formating can have up to 3 different formulas. The conditional
formating defaults to one formula and you can add two more using the ADD button in the conditional formating window. Put the same formula in each of the three ADD windows but select a different color for each formula. The formulas wioll differ by the string "XT". You didn't indicate which row your data was located. If XT is cell A4 then put this formula in the conditional formating for cell A4 =($A4="XT") "andrew" wrote: Additional question to below. Assuming if i have more than 3 different text to monitor (each with its own color to fill) is it still possible using Conditional Formatting? This is because the table i'm working with (per below) has up to 5 different "text" to monitor in the same worksheet and highlight selected cells in the row assuming if there is a match. "andrew" wrote: Hi Joel, what if the first cell is not with the text "XT"? Does it need a cell to refer to? See below on my actual table: A B C D E 1/1 13 A1 C3 A2 8/1 23 C3 C3 A2 15/1 13 A2 A1 C3 22/1 XT C3 A2 C3 Column A shows the date. Column B is where i would want the formula to be 'activated' only when the text "XT" appears. When it does (in this case, B4), it will then select row 4 from A-D and highlighted it with green (color). If there's no "XT" appearing in the column then all is normal. In my actual table, row 1-5 has a different data layout, then row 6 onwards is the table you see above. Should i use UDF instead? "Joel" wrote: Use conditional formating (menu Format - conditional formating) If the first ccell is A1 then in conditional formating chage "Cell Value Is" to "formula Is) 1) enter the formula below =($A1="XT") 2) Set format Pattern (conditional formating window) to the color you want. 3) copy cell A1 then select all the cells in your table and PasteSpecial using FORMAT which will copy only the conditional format to all the cells. "andrew" wrote: I hv the following table: A B C D XT A1 C3 A2 23 C3 C3 A2 13 A2 A1 C3 XT C3 A2 C3 Is there a formula to check column A for the specific text "XT", and then color the row (A-D) with the color of choice? What happens is that the row is incrementally updated with data every week, hence whenever a new row has column A with the text "XT", it should automatically convert row cells of A-D (where "XT" resides) into a color of choice. Possible? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HOW TO COLOR CELLS SELECTED WITH THE CURSOR | Excel Discussion (Misc queries) | |||
Color of selected cells | Excel Discussion (Misc queries) | |||
How do I change highlight color of selected cells | Excel Discussion (Misc queries) | |||
How do i change the color of the selected cells? | Excel Discussion (Misc queries) | |||
how do i change the selected cells color | New Users to Excel |