Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Conditional Formatting Or Select Case

  Asked By: Darla    Date: Aug 25    Category: MS Office    Views: 697
  

i need to make formatting to cells in column D depending on values in
column B
if value in column B3 = 1 then D3= D65500 (+ .025 or -.025)
B3 = 2 then D3= D65501 (+ .025 or -.025)
B3 = 3 then D3= D65502 (+ .025 or -.025)
B3 = 4 then D3= D65503 (+ .025 or -.025)
B3 = 5 then D3= D65504 (+ .001 or -.001)
B3 = 6 then D3= D65505 (+ .001 or -.001)

example if B3=1 and D65500 = 5.75 the value in D3 can be between
(5.725 - 5.775) only else
its (D3) colored with red
how to do that ??

Share: 

 

5 Answers Found

 
Answer #1    Answered By: Madiha Malik     Answered On: Aug 25

I wasn't even going to try -- the description made little sense to me...

 
Answer #2    Answered By: Ned Storm     Answered On: Aug 25

If Range("b" & Target.Row).Value = 1 Then
If Range("d" & Target.Row).Value < Range("d65530") - 0.07 Or Range("d" &
Target.Row).Value > Range("d65530") + 0.07 Then
Range("d" & Target.Row).Font.ColorIndex = 3
Else
Range("d" & Target.Row).Font.ColorIndex = 1
End If
End If
If Range("b" & Target.Row).Value = 2 Then
If Range("d" & Target.Row).Value < Range("d65529") - 0.07 Or Range("d" &
Target.Row).Value > Range("d65529") + 0.07 Then
Range("d" & Target.Row).Font.ColorIndex = 3
Else
Range("d" & Target.Row).Font.ColorIndex = 1
End If
End If
If Range("b" & Target.Row).Value = 3 Then
If Range("d" & Target.Row).Value < Range("d65532") - 0.05 Or Range("d" &
Target.Row).Value > Range("d65532") + 0.05 Then
Range("d" & Target.Row).Font.ColorIndex = 3
Else
Range("d" & Target.Row).Font.ColorIndex = 1
End If
End If
If Range("b" & Target.Row).Value = 4 Then
If Range("d" & Target.Row).Value < Range("d65531") - 0.05 Or Range("d" &
Target.Row).Value > Range("d65531") + 0.05 Then
Range("d" & Target.Row).Font.ColorIndex = 3
Else
Range("d" & Target.Row).Font.ColorIndex = 1
End If
End If
i use this code and its work good
but i need to use conditional  formatting Or Select Case

 
Answer #3    Answered By: Catherine Campbell     Answered On: Aug 25

I think you will not be able to use conditional  formatting because you are
limited, I think, to 3 options.

You could simplify your formula by
Putting the error limits in E65531 onwards
Then using


tablestart = range("D65530)
Selector = Range("b" & Target.Row).value
AimValue=Tablestart.offset(selector,0).value
Errorlimit=Tablestart.offset(selector,1).value

If Target.Row value < aimvalue - errorlimit or target.row >
aimvalue+errorlimit then
Range("d" & Target.Row).Font.ColorIndex = 3
Else
Range("d" & Target.Row).Font.ColorIndex = 1
End if

 
Answer #4    Answered By: Janet Phillips     Answered On: Aug 25

I think you could use a conditional  format with a single condition of
something like:

=OR(D16<CHOOSE(B16,$D$65530-0.07,$D$65529-0.07,$D$65532-0.05,$D$65531-0.05),
D16>CHOOSE(B16,$D$65530+0.07,$D$65529+0.07,$D$65532+0.05,$D$65531+0.05))

Change the specifics as you need them, since your two examples had
different lists of conditions...

 
Answer #5    Answered By: Jacob Bouchard     Answered On: Aug 25

The problem with what you are describing (or at least what I think you are
describing - it's very vague) is that your conditional  formatting formula is
going to have to look at B3 and make the same decisions that your main
formula has already made. While this is perfectly possible, it's
cumbersome.

You should really bring your selected limits up onto row 3 as upper/lower.
Then it's easy enough to compare the value against your upper and lower
limits in your conditional formatting  statement.

 
Didn't find what you were looking for? Find more on Conditional Formatting Or Select Case Or get search suggestion and latest updates.




Tagged: