conditional formatting

  • Thread starter Thread starter herman
  • Start date Start date
H

herman

Thanks yesterday for the help but I'm still not there
I have a large range of cells that I wish to CF the
solution I was given yesterday was
CF=exact(mid(a1,2,1),"N") then what my format. What I have
is a range of cells from "A1" to "HA17" where I input
either P1,P2,P3,P5,H1,H2,H6,G1,G2,G6,PN1,GN2,HN4.... etc
into any of the cells within the mentioned range what I
need to do is that any cell within that range has a "N" in
it to change format (green background) I have tried to
highlite the range and then do a CF as above but any cell
that has a "N" doesn't change to green background but all
cells that are 7 right and 4 down form cell with "N"
in it change format??? tried changing "A1" in formula to
range "A1:AH17" to no avail, found that by typing,
exact(mid(a1,2,1),"N") in each cell and changing "A1" to
represent each cell works but that'a a lot of typing. Ther
must be an easier way.
 
You should be able to do cf for your entire range by
following the steps below.

1. Select your entire range, starting with A1.
2. Format > Conditional Formatting > Formula Is:
=EXACT(MID(A1,2,1),"N")
3. Click the "Format" button and format as desired.
4. Press OK twice.

BTW - yesterday you wanted to color any cell where the 2nd
char. is "N". Now you're saying if the cell just
contains "N". Which is it?

HTH
Jason
Atlanta, GA
 
herman said:
Thanks yesterday for the help but I'm still not there
I have a large range of cells that I wish to CF the
solution I was given yesterday was
CF=exact(mid(a1,2,1),"N") then what my format. What I have
is a range of cells from "A1" to "HA17" where I input
either P1,P2,P3,P5,H1,H2,H6,G1,G2,G6,PN1,GN2,HN4.... etc
into any of the cells within the mentioned range what I
need to do is that any cell within that range has a "N" in
it to change format (green background) I have tried to
highlite the range and then do a CF as above but any cell
that has a "N" doesn't change to green background but all
cells that are 7 right and 4 down form cell with "N"
in it change format??? tried changing "A1" in formula to
range "A1:AH17" to no avail, found that by typing,
exact(mid(a1,2,1),"N") in each cell and changing "A1" to
represent each cell works but that'a a lot of typing. Ther
must be an easier way.
Click to expand...

I don't quite see why there is a problem.
However, if you have successfully conditionally formatted A1 with the
formula
=exact(mid(a1,2,1),"N")
you can use the format painter to copy the format and paste it over your
whole range in one operation.
 
Hi Herman,
I think your problem is that you had Cell H8 active when you entered
the formula instead of cell A1. Take a look at my page
. http://www.mvps.org/dmcritchie/excel/condfmt.htm

Only select the cells (usually columns) that you want to be colored,
they do not have to be contiguous.

The formula you enter is relative to the active cell.

I don't really follow what you want. The idea of C.F.
is to generate a True (not equal 0) or False (equal to 0)
condition. If you want to find an "N" anywhere within
the cell and A1 is your Active cell when you enter the
formula try: =FIND("N",A1,1) if letter case is
must be same -- capital "N".

Try =SEARCH("n",A1,1) if letter case does not
matter. A capital "N" would give same result.
 
Back
Top