colour in a range

K

Kevin

Hi

i have an upper and lower values in columns B and C
the user enters value in columns D to X
what i want to do is check over a range from column D to X
if any of the entered values are outside the ranges in columns B and C
i want the cell to change colour(ie Red)
if the vlaues are in sided the range i want the cell to turn green

can anyone help with Vb code for this

thanks in advance


kevin
 
B

Bob Phillips

Why not use conditional formatting, with a formula of say

=D1=MAX(B1:C100)

etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
J

Jim May

**Maximize this screen to more easily read///
Go to your first Cell instance say D3 - Format, Conditional formatting;
Use Formula Is (in all 3 cases);

Under In Box type
Click Format Button and select
Condition1 =ISBLANK(D3) No
Color
Click Add>> button
Condition2 =IF(OR(D3>$B3,D3<$C3),TRUE,FALSE) Red
Click Add>> button
Condition3 =IF(OR(D3<=$B3,D3<=$C3),TRUE,FALSE) Green
OK out of screen.

With d3 your current cell do the Format-Painter thing to al other cells in
D:X range
A real expert/guru can probably do this in 1 STEP, but anyway

HTH
 
B

Bob Phillips

As an example

Select the cells in D1:X100 or whatever the range is
Menu Format>Conditional Formatting
Change Condition 1 to Formula Is
Add a formula of =D1=MAX(B1:C100)
Click Format
Select the Pattern tab and choose a colour
OK back
Click Add >>
Repeat with a formula of =D1=MIN(B1:C100) and a different colour
OK out


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
B

Bob Phillips

Select the cells in D1:X100 or whatever the range is
Menu Format>Conditional Formatting
Change Condition 1 to Formula Is
Add a formula of =D1=MAX(B1:C100)
Click Format
Select the Pattern tab and choose a colour
OK back
Click Add >>
Repaet witgh a formula of =D1=MIN(B1:C100) and a different colour
OK out

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Top