Color a certain range of cell if a cell contain specified word

C

crapit

E.g
a b c d e
f g h
1
2
3 Group1
4
5
6
7
8
9 Group2

if a cell contain the word "Group" + a single char, i want the row which is
the same the cell containing "Group" to be autohighted

In tis case, it will be Cell A3-H3, and Cell A9-H9 to be auto-highlight!
 
D

Don Guillett

Put cursor on col A>format>conditional format>formula
is>=left($a4,5)="group">format as desired>copy format (use format tool) to
col e then copy format the row down/up as desired.
 
D

David McRitchie

Perhaps a little closer to your specifics.

Since you want the entire row to be highlighted:
Select all cells on the worksheet with cell A1 as the active cell
since $A1 is used that actual means any cell on row 1 could
be the active cell for this purpose.

The conditional formula is based on the active cell. So if A1 is
the active cell the formula might be:
=AND(LEN(TRIM($A1))=6,LEFT($A1,5)="group")
This is assuming that you really meant the word group followed
by exactly one character as you asked for. Trim is used so that
a single space will not count as the character, and the length is
checked to insure that only a single character is appended per
your request. Though I suspect that Don's assumption that all
you really wanted was to make sure that the cell begins with
"group" and without respect to capitalization in which case:
=LEFT($A1,5)="group")
if A1 is your active cell when entering the C.F.

The conditional format will apply to all cells selected, and all cells
were selected so that the entire row will be highlighted. Each cell
in the selection is tested. So every cell in row 1 is tested based
on the content of cell A1. Every cell in row 2 is tested based on the
content of cell A2, etc. -- that is why Don placed the $ in front of the A.
But from his testing it should be noted that cell A4 was the active cell
when he tested.

The fact that every cell on the sheet is tested is not a concern within
Excel itself it will be very fast and built in to Excel. You would not want to
test every cell on a worksheet with VBA (your own macro coding).

My page on Conditional Formatting is
http://www.mvps.org/dmcritchie/excel/condfmt.htm
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top