Conditional formatting formulas odd even numbers

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,353
Office Version
  1. 2010
Hello,

From data column B5:F13 I want Conditional formatting formulas odd even numbers like this is it possible

Odds between 1 to 23 fonts White highlight colour Red

Odds between 25 to 49 fonts White highlight colour Magenta

Evens between 2 to 24 fonts White highlight colour Green

Evens between 25 to 50 fonts White highlight colour Blue


*ABCDEFGH
1
2
3
4
5913212935
623131721
7210164250
81621273032
9732394045
101628324448
111234373842
121823374142
13210183435
14410273840
15
16
17
18


Thank you all.

I am using Excel 2000

Regards,
Moti
 

Attachments

  • Conditional formatting formulas odd even numbers.png
    Conditional formatting formulas odd even numbers.png
    13.6 KB · Views: 16

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Would a macro be ok?
VBA Code:
Sub motilulla()
   Dim Cl As Range
   For Each Cl In Range("B5:F14")
      Select Case True
         Case Cl.Value Mod 2 = 1 And Cl.Value < 24
            Cl.Interior.Color = vbRed
         Case Cl.Value Mod 2 = 1 And Cl.Value > 23
            Cl.Interior.Color = vbMagenta
         Case Cl.Value Mod 2 = 0 And Cl.Value < 25
            Cl.Interior.Color = rgbDarkGreen
         Case Cl.Value Mod 2 = 0 And Cl.Value > 24
            Cl.Interior.Color = vbBlue
      End Select
      Cl.Font.Color = vbWhite
   Next Cl
End Sub
 
Upvote 0
Solution
Select Range From B5 to Last Go to Conditional Formating, Manage Rule, New Rule, Use a formula And Type This For Condition 1 to 4 And Select Colors That you want:
Odds between 1 to 23 fonts White highlight colour Red
Excel Formula:
=AND(B5>0,B5<24,MOD(B5,2)=1)
Odds between 25 to 49 fonts White highlight colour Magenta
Excel Formula:
=AND(B5>24,B5<50,MOD(B5,2)=1)
Evens between 2 to 24 fonts White highlight colour Green
Excel Formula:
=AND(B5>0,B5<24,MOD(B5,2)=0)
Evens between 25 to 50 fonts White highlight colour Blue
Excel Formula:
=AND(B5>25,B5<51,MOD(B5,2)=0)
 
Upvote 0
@maabadi
Unfortunately your suggestion wont work as the OP is using Xl2000, which is limited to 3 conditions.
 
Upvote 0
Would a macro be ok?
VBA Code:
Sub motilulla()
   Dim Cl As Range
   For Each Cl In Range("B5:F14")
      Select Case True
         Case Cl.Value Mod 2 = 1 And Cl.Value < 24
            Cl.Interior.Color = vbRed
         Case Cl.Value Mod 2 = 1 And Cl.Value > 23
            Cl.Interior.Color = vbMagenta
         Case Cl.Value Mod 2 = 0 And Cl.Value < 25
            Cl.Interior.Color = rgbDarkGreen
         Case Cl.Value Mod 2 = 0 And Cl.Value > 24
            Cl.Interior.Color = vbBlue
      End Select
      Cl.Font.Color = vbWhite
   Next Cl
End Sub

Fluff, yes the VBA solved my query. It worked fine. Thank you very much for your help​


Kind Regards
Moti :)
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top