Bit odd conditional formatting

M

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,422
Office Version
  1. 2010
Hello,</SPAN></SPAN>

I want to set conditional formatting in the columns T:AB using bit odd criteria for example... </SPAN></SPAN>
1-Row T4:AB7 has numbers 0, 1 & 2--->Look these numbers in the row D7:Q7 if have match 0 in the row D8:Q8 if yes then fill colour red and font white in the all (0, 1 & 2)</SPAN></SPAN>

2-Row T5:AB5 has numbers 0, 1 & 2--->Look these numbers in the row D8:Q8 if have match 0 in the row D9:Q9 if yes then fill colour red and font white in this case num 1 is in F8 do not have 0 in the F9 so far just fill colour red and font white in the 0 & 2 only.</SPAN></SPAN>

Example data.... </SPAN></SPAN>


Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAAB
1
2
3
4
5n1n2n3n4n5n6n7n8n9n10n11n12n13n14CF1CF2CF3CF4CF5CF6CF7CF8CF9
6
701000111210101012
800100000000210012
91050402101002001245
10210001321011000123
110131207012016210012371216
1202111040002010
Sheet1


Thank you all</SPAN></SPAN>

Excel 2000</SPAN></SPAN>
Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>
 
Last edited:
Sort by date Sort by votes
Hm, maybe SpecialCells xlLogical do not exist in Excel 2000.
Let's try using SpecialCells xlTextValues.

Code: 
[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1082697b()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1082697-bit-odd-conditional-formatting.html[/COLOR][/I]
[COLOR=Royalblue]Dim[/COLOR] rng [COLOR=Royalblue]As[/COLOR] Range
[COLOR=Royalblue]Dim[/COLOR] n [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], j [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], k [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], s [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] va [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Variant[/COLOR], vb [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Variant[/COLOR], vc [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Variant[/COLOR]

Application.ScreenUpdating = [COLOR=Royalblue]False[/COLOR]
n = Range([COLOR=brown]"D"[/COLOR] & Rows.count).[COLOR=Royalblue]End[/COLOR](xlUp).Row
va = Range([COLOR=brown]"D7:Q"[/COLOR] & n)
[COLOR=Royalblue]Set[/COLOR] rng = Range([COLOR=brown]"T7:AB"[/COLOR] & n - [COLOR=crimson]1[/COLOR])
vb = rng
vc = rng
[COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR]) - [COLOR=crimson]1[/COLOR]
    [COLOR=Royalblue]For[/COLOR] k = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(vb, [COLOR=crimson]2[/COLOR])
    
        [COLOR=Royalblue]If[/COLOR] vb(i, k) <> [COLOR=brown]""[/COLOR] [COLOR=Royalblue]Then[/COLOR]
            s = vb(i, k)
            [COLOR=Royalblue]For[/COLOR] j = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(va, [COLOR=crimson]2[/COLOR])
                [COLOR=Royalblue]If[/COLOR] va(i, j) = s [COLOR=Royalblue]And[/COLOR] va(i + [COLOR=crimson]1[/COLOR], j) = [COLOR=crimson]0[/COLOR] [COLOR=Royalblue]Then[/COLOR]
                    [COLOR=Royalblue]If[/COLOR] i [COLOR=Royalblue]Mod[/COLOR] [COLOR=crimson]2[/COLOR] = [COLOR=crimson]0[/COLOR] [COLOR=Royalblue]Then[/COLOR]
                        vb(i, k) = [COLOR=brown]"YES"[/COLOR]
                        [COLOR=Royalblue]Else[/COLOR]
                        vb(i, k) = [COLOR=brown]"#N/A"[/COLOR]
                        [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]For[/COLOR]
                    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
                [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
            [COLOR=Royalblue]Next[/COLOR]
        [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
    
    [COLOR=Royalblue]Next[/COLOR]
[COLOR=Royalblue]Next[/COLOR]
rng = vb

    [COLOR=Royalblue]With[/COLOR] rng
        .SpecialCells(xlConstants, xlErrors).Font.Color = vbWhite
        .SpecialCells(xlConstants, xlErrors).Interior.Color = vbRed
        .SpecialCells(xlConstants, xlTextValues).Font.Color = vbWhite
        .SpecialCells(xlConstants, xlTextValues).Interior.Color = vbBlue
    [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR]

rng = vc
Application.ScreenUpdating = [COLOR=Royalblue]True[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]
 
Upvote 0

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Akuini said:
Hm, maybe SpecialCells xlLogical do not exist in Excel 2000.
Let's try using SpecialCells xlTextValues.

Code: 
[FONT=lucida console][COLOR=royalblue]Sub[/COLOR] a1082697b()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1082697-bit-odd-conditional-formatting.html[/COLOR][/I]
[COLOR=royalblue]Dim[/COLOR] rng [COLOR=royalblue]As[/COLOR] Range
[COLOR=royalblue]Dim[/COLOR] n [COLOR=royalblue]As[/COLOR] [COLOR=royalblue]Long[/COLOR], j [COLOR=royalblue]As[/COLOR] [COLOR=royalblue]Long[/COLOR], k [COLOR=royalblue]As[/COLOR] [COLOR=royalblue]Long[/COLOR], i [COLOR=royalblue]As[/COLOR] [COLOR=royalblue]Long[/COLOR], s [COLOR=royalblue]As[/COLOR] [COLOR=royalblue]Long[/COLOR]
[COLOR=royalblue]Dim[/COLOR] va [COLOR=royalblue]As[/COLOR] [COLOR=royalblue]Variant[/COLOR], vb [COLOR=royalblue]As[/COLOR] [COLOR=royalblue]Variant[/COLOR], vc [COLOR=royalblue]As[/COLOR] [COLOR=royalblue]Variant[/COLOR]

Application.ScreenUpdating = [COLOR=royalblue]False[/COLOR]
n = Range([COLOR=brown]"D"[/COLOR] & Rows.count).[COLOR=royalblue]End[/COLOR](xlUp).Row
va = Range([COLOR=brown]"D7:Q"[/COLOR] & n)
[COLOR=royalblue]Set[/COLOR] rng = Range([COLOR=brown]"T7:AB"[/COLOR] & n - [COLOR=crimson]1[/COLOR])
vb = rng
vc = rng
[COLOR=royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR]) - [COLOR=crimson]1[/COLOR]
    [COLOR=royalblue]For[/COLOR] k = [COLOR=crimson]1[/COLOR] [COLOR=royalblue]To[/COLOR] UBound(vb, [COLOR=crimson]2[/COLOR])
    
        [COLOR=royalblue]If[/COLOR] vb(i, k) <> [COLOR=brown]""[/COLOR] [COLOR=royalblue]Then[/COLOR]
            s = vb(i, k)
            [COLOR=royalblue]For[/COLOR] j = [COLOR=crimson]1[/COLOR] [COLOR=royalblue]To[/COLOR] UBound(va, [COLOR=crimson]2[/COLOR])
                [COLOR=royalblue]If[/COLOR] va(i, j) = s [COLOR=royalblue]And[/COLOR] va(i + [COLOR=crimson]1[/COLOR], j) = [COLOR=crimson]0[/COLOR] [COLOR=royalblue]Then[/COLOR]
                    [COLOR=royalblue]If[/COLOR] i [COLOR=royalblue]Mod[/COLOR] [COLOR=crimson]2[/COLOR] = [COLOR=crimson]0[/COLOR] [COLOR=royalblue]Then[/COLOR]
                        vb(i, k) = [COLOR=brown]"YES"[/COLOR]
                        [COLOR=royalblue]Else[/COLOR]
                        vb(i, k) = [COLOR=brown]"#N/A"[/COLOR]
                        [COLOR=royalblue]Exit[/COLOR] [COLOR=royalblue]For[/COLOR]
                    [COLOR=royalblue]End[/COLOR] [COLOR=royalblue]If[/COLOR]
                [COLOR=royalblue]End[/COLOR] [COLOR=royalblue]If[/COLOR]
            [COLOR=royalblue]Next[/COLOR]
        [COLOR=royalblue]End[/COLOR] [COLOR=royalblue]If[/COLOR]
    
    [COLOR=royalblue]Next[/COLOR]
[COLOR=royalblue]Next[/COLOR]
rng = vb

    [COLOR=royalblue]With[/COLOR] rng
        .SpecialCells(xlConstants, xlErrors).Font.Color = vbWhite
        .SpecialCells(xlConstants, xlErrors).Interior.Color = vbRed
        .SpecialCells(xlConstants, xlTextValues).Font.Color = vbWhite
        .SpecialCells(xlConstants, xlTextValues).Interior.Color = vbBlue
    [COLOR=royalblue]End[/COLOR] [COLOR=royalblue]With[/COLOR]

rng = vc
Application.ScreenUpdating = [COLOR=royalblue]True[/COLOR]
[COLOR=royalblue]End[/COLOR] [COLOR=royalblue]Sub[/COLOR][/FONT]
Click to expand...
Akuini, :pray: Just come back to desk computer right now tried modified VBA code you send it is working flawless very correct! And I could save the workbook without any trouble</SPAN></SPAN>

Thank you very much for the help!</SPAN></SPAN>

Query solved!</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Moti </SPAN></SPAN>:grin:</SPAN></SPAN>
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question Question
Advice for conditional formatting not working
Replies
1
Views
343
rlv01
R
K
  • Question Question
Conditional Formating - Dynamic "not between"
Replies
3
Views
568
dreid1011
dreid1011
Cliffork
  • Solved
Conditional Formatting not working properly with AND function
Replies
4
Views
158
Joe4
Joe4
R
  • Question Question
Excel Conditional Formatting Formula to change fill colour based on position of active cell
Replies
2
Views
853
rasinc
R
gtd526
  • Question Question
Conditional Formatting for Matching Odd Rows only
Replies
4
Views
264
gtd526
gtd526

Forum statistics

Threads
1,224,825
Messages
6,181,191
Members
453,021
Latest member
pingpong7117

Share this page

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