Just a conditional

[renartbrazil]

By the way the site is looking much better, nice.
I have numbers in column D, they range from zero to 1500. The format is 42300 for $423.00. So I just want to make conditional formatting color that space if
Cell Value is Between 45000 and 49900 then color BLUE.
Which I have gotten that part and I have made it into a Macro also.
The thing I want to do is have two cells, one that I can put a lower value like 45000 in top cell then put the higher value like 49900 in bottome cell. So not really sure how I can get this same result going this way or is there something else I can do to get these two cells to operate like that? Thanks.+

 

[KenWright]

Assuming your values were in A1:A30 and your two trigger cells were E1 and E2.

Format / Cond Formatting, change 'cell value is' to 'Formula is' and use:-

=AND(A1>=$E$1,A1<=$E$2)

Choose the pattern or font and you are done.

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[renartbrazil]

I could not get it to work properly with even tring that example. Need to keep trying, any ideas?

 

[GlennUK]

What do you mean by "I could not get it to work properly"? In what way did it work? In what way didn't it work? What exactly did you try?

Have you realised that the $ in Ken's example are very important?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[KenWright]

Especially the LACK of $ on the A1 s. Probably the most important thing though is the range selected - If you select A1:A30 then the formula is fine, in fact if you select A1:Anything then the formula is fine, BUT if you are selecting a different range ie starting in say B4 or something, then the formula must be amended to use that starting cell, eg:-

=AND(B4>=$E$1,B4<=$E$2)

again with NO $ signs.

The cell in the formula must be the top left of the range you selected.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[renartbrazil]

http://www.geocities.com/renartbrazil/TEKTIPTEST110104.xls

This is an example file. I did get it to work somewhat but seem to have an issue with the format. I believe it is general or no format when it is imported into the Excel spreadsheet from a database. Now when it is in Excel, no matter which format I choose it seems to only highlight in some formats but will not highlight the correct ones. Cant seem to have it read as numbers to finish the task. Any ideas? Thank you

 

[KenWright]

Your problem is that your data is TEXT and not numeric. If you are not going to do any data cleansing when you bring your data in then you are goiung to have to cater for it within your formula, but personally I prefer to fix the data. That having been said, format your cells B1 and b2 as NUMBER or GENERAL and hit F2 on each and re-enter them so they appear as numeric. Now amend your conditional format to read as follows:-

=AND(--A4>=$B$1,--A4<=$B$2)

Note the additional double unary operators which will effectively coerce each cell to a numeric value.

Use the same logic on your other ranges as well.

Regards
Ken...................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------