Conditional formatting formulas odd even numbers

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
1,843
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: 7

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
57,000
Office Version
  1. 365
Platform
  1. Windows
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
 
Solution

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,847
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
57,000
Office Version
  1. 365
Platform
  1. Windows
@maabadi
Unfortunately your suggestion wont work as the OP is using Xl2000, which is limited to 3 conditions.
 

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
1,843
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 :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
57,000
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,381
Messages
5,641,823
Members
417,239
Latest member
AymericA

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
Top