Conditional Formatting

[dxzaber]

cell B1=10

if cell A1 value is greater than B1 (say A1=11 ) , I want Cell B1 to be filled with RED

if cell A1 value is less than B1 and B1 is larger by 10% or less (say A1=9.1,9.8,9.5) , I want Cell B1 to be filled with GREEN

if cell A1 value is less than B1 and B1 is larger by 10.1% to 20% more (say A1=8.9,8.5) , I want Cell B1 to be filled with Blue

if cell A1 value is less than B1 and B1 is larger by 20.1% or more (say A1=7.8,7) , I want Cell B1 to be filled with Cyan


What should be formula??

 

[oldbrewer]

100	75	red
100	107	green
100	113	blue
100	122	cyan
				I did this by colouring all the cells red
				then applying 3 levels of conditional formatting
				(I only have excel 2000 so limited to 3 conditions)
				in order they are
				=AND(A1<B1,B1<1.1*A1)=TRUE
				=AND(A1<B1,B1>1.1*A1,B1<1.2*A1)=TRUE
				=AND(A1,B1,B1>1.2*A1)=TRUE

<colgroup><col span="10"></colgroup><tbody>
</tbody>

 

[TonyUK72]

Conditional formatting is limited to 3 options as far as im aware (Excel 2010 & below) this can be bypassed using VBA

The code below will start you off, but you will need to amend the code in order to effect the colours that represent you 2nd and 3 option.

Sub ConFormat4()
' Automatically Runs On Cell Change


' Counter For Column Range
Dim i%
For i = 1 To 5


' Actions Formating Based On Cell Value (>, <10%, <20%, >20.1% or "")


'Option 1 A1 > B1
If Range("A" & i).Value > Range("B" & i).Value Then
Range("B" & i).Interior.ColorIndex = 3
End If


'Option 2 A1 upto 10% less
If Range("A" & i).Value < ?? Then
Range("B" & i).Interior.ColorIndex = 4
        End If


'Option 3 A1 between 10.1% & 20%
If Range("A" & i).Value < ?? Then
Range("B" & i).Interior.ColorIndex = 41
            End If


'Option 4 A1 less than B1 by 20.1% or more
If Range("A" & i).Value < Range("B" & i).Value Then
Range("B" & i).Interior.ColorIndex = 20
End If


'Option 5 A1 contains no value
If Range("A" & i).Value = "" Then
Range("B" & i).Interior.ColorIndex = 0
Else
    End If
Next i


End Sub

The following code needs to be placed in the MEO Sheet1(Sheet1)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call ConFormat4
End Sub

 

[dxzaber]

Conditional formatting is limited to 3 options as far as im aware (Excel 2010 & below) this can be bypassed using VBA

The code below will start you off, but you will need to amend the code in order to effect the colours that represent you 2nd and 3 option.

Sub ConFormat4()
' Automatically Runs On Cell Change


' Counter For Column Range
Dim i%
For i = 1 To 5


' Actions Formating Based On Cell Value (>, <10%, <20%, >20.1% or "")


'Option 1 A1 > B1
If Range("A" & i).Value > Range("B" & i).Value Then
Range("B" & i).Interior.ColorIndex = 3
End If


'Option 2 A1 upto 10% less
If Range("A" & i).Value < ?? Then
Range("B" & i).Interior.ColorIndex = 4
        End If


'Option 3 A1 between 10.1% & 20%
If Range("A" & i).Value < ?? Then
Range("B" & i).Interior.ColorIndex = 41
            End If


'Option 4 A1 less than B1 by 20.1% or more
If Range("A" & i).Value < Range("B" & i).Value Then
Range("B" & i).Interior.ColorIndex = 20
End If


'Option 5 A1 contains no value
If Range("A" & i).Value = "" Then
Range("B" & i).Interior.ColorIndex = 0
Else
    End If
Next i


End Sub

The following code needs to be placed in the MEO Sheet1(Sheet1)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call ConFormat4
End Sub

THANKS FOR YOUR REPLY. I AM NOT GOOD at macros/VBA .So have decided to omit last stage now its in three color (excel 2013)

cell B1=10

if cell A1 value is greater than B1 (say A1=11 ) , I want Cell B1 to be filled with RED

if cell A1 value is less than B1 and B1 is larger by 10% or less (say A1=9.1,9.8,9.5) , I want Cell B1 to be filled with GREEN

if cell A1 value is less than B1 and B1 is larger by 10.1% or more (say A1=8.9,8.5) , I want Cell B1 to be filled with Blue

Now what will be formula???

 

[oldbrewer]

my post was spoilt by yhe < > signs effect - sorry

My solution gives you 4 colours BECAUSE they are all red to start with then 3 more colours are selected or not

 

[oldbrewer]

=a1 > b1 and color red
=and(a1 < b1,b1 < 1.1*a1) = true color green
=and(a1 < b1,b1 > 1.1*a1 = true color blue