Conditional Format

[stevekirk]

HI,

this is the data i have in column d starting at rows 6 . this data could vary and could be 200 rows long

RH SIDE SPLAYED
LH SIDE 2100X718
TOP /BOTTOM 1664X699
TOP /BOTTOM 1764X699
BACK BOARD 1982X882
BACK 1982X832
RH DIVIDER 1982X582
SHELF 864X560
SHELF
SHELF 814X560
PLINTH 1664X82 1=2
PLINTH 1764X82 1=2

the conditon i have at the minute is
=not(iserror(search("splayed"),d6)))
this will highlight red
what i need to also do is that if there is no numbers at the end of the text
also highlight red . see "shelf "as an exapmle

thanks
steve

[oldchippy]

Try this,

=ISNUMBER(RIGHT(A1,1)*1)=FALSE

[stevekirk]

Thanks,

could you please advise on this one
this is what i use on one conditional and it works great
=and(isblank(e8),(a8>0))
i have tried this on another worksheet using column d not e but it will not work because there is a formular in column d could you please modify it to suit

thanks
steve

[BigBas]

EDIT: the forumla did not work, try this one:

=and(NOT(ISNUMBER($d$8)),(a8>0))

[stevekirk]

hi,

please see attached file sheet 1 is where tha data is, sheet2 is the link sheet
sheet 1 row 4 column "a" has "d" in it column d row 4 has no data in it

sheet 2 column "d" row "4 " i needs to turn "red " as this indicates no size has been put in sheet 1

sheet 2 is the document that is supplied to the shop floor

thanks

[oldchippy]

Delete the condition you have on D4, then change it to:-

Condition 1 - Cell value is - less than or equal to - 0

Now you could format the cell to RED, which is what you asked for and apply the same formatting to the remainder of the column with the PaintBrush icon, or you could format the font to WHITE so that the cell displays "blank" until it gets a value above 0, up to you?

[stevekirk]

the condition is not working correctly it is only looking at column d it needs i think to see if there is any data other than the formular in column "a" if not make the condition work


steve

[oldchippy]

If you follow every step in my last post, it will work as requested

[stevekirk]

hi,

i think i have done what you have told me but it is not highlighting just "d4"
please see file attached

thanks
steve

[oldchippy]

If you want to apply the same formatting to any cell in any column, click on one of the RED cells, click the Painbrush icon and click on the range of cells in any column/or all columns you wish to apply the same formatting to. Does that work for you?

[stevekirk]

HI,

no, I need the cell in column "d" to turn red only if there is nothing in it except the "0" yet there is something in column "a" other than the "0"

steve

[Bryan Hessey]

HI,

no, I need the cell in column "d" to turn red only if there is nothing in it except the "0" yet there is something in column "a" other than the "0"

steve

select column D (D1 as the active cell) and CF with formula

=AND(A1<>0,D1=0)


this disagrees with your first post, but appears correct for your last post.

for your first post, you would need the formula

=IF(AND(D1=0,LEN(A1)>1,ISNUMBER(VALUE(RIGHT(A1,1)))),1,0)

(adjust to row 6 if you need to avoid rows 1 to 5)

hth
---amended since you read!

[stevekirk]

thanks bryan,
works ok

the first post was solved ok, because i was doing conditional formating the second question was asked.

i should have used another post(sorry)

steve

[Bryan Hessey]

thanks bryan,
works ok

the first post was solved ok, because i was doing conditional formating the second question was asked.

i should have used another post(sorry)

steve

aaha - ok, it's almost Cinderella time, everything goes a little fuzzy, but good to see that you have a working solution.

---

hi oldchippy