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:

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
Maybe...

Select T7:AB11

In Conditional Formatting use this formula
=AND(T7<>"",SUMPRODUCT(--($D7:$Q7=T7),--($D8:$Q8=0))>0)
Format button and pick a format

M.
 
Upvote 0
Marcelo Branco said:
Maybe...

Select T7:AB11

In Conditional Formatting use this formula
=AND(T7<>"",SUMPRODUCT(--($D7:$Q7=T7),--($D8:$Q8=0))>0)
Format button and pick a format

M.
Click to expand...
Marcelo Branco, Your CF formula worked at glance. Thank you very much for the help!</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Moti :-D</SPAN></SPAN>
 
Upvote 0
Hello, again when I apply the formula and use one colour format it works perfect approximately with 8000+ rows, I am having problem when I use 2 different colour format I can not save the workbook it says all format will be loss may it can be due to memory problem not sure </SPAN></SPAN>
Like this...</SPAN></SPAN>


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


Is there any VBA solution to solve it by using 2 different colour formats? </SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Moti </SPAN></SPAN>
 
Last edited:
Upvote 0
Hello,</SPAN></SPAN>

I tried again using the formula and changing the conditions for alternate row different Red & Blue but it seems that does not work. Workbook close with message there is not enough memory and when I force to close I see all CF is lost. "Using one colour no problem"

I think may be just to colour the row with alternate colour Red & Blue instead formula can be the VBA solution only.</SPAN></SPAN>

Please help</SPAN></SPAN>


Regards,</SPAN></SPAN>
Moti </SPAN></SPAN>
 
Last edited:
Upvote 0
The following has been tested to work in a workbook with 20,000+ rows of data:

1. Clear CF rules from range T:AB
2. Apply the following CF rule to range $T$7:$AB$20000 for the white-on-blue formatting =AND(ISEVEN(ROW()),T7<>"",SUMPRODUCT(--($D7:$Q7=T7),--($D8:$Q8=0)))
3. Apply the following CF rule to range $T$7:$AB$20000 for the white-on-red formatting =AND(ISODD(ROW()),T7<>"",SUMPRODUCT(--($D7:$Q7=T7),--($D8:$Q8=0)))
Note: change 20000 as needed
 
Upvote 0
Tetra201 said:
The following has been tested to work in a workbook with 20,000+ rows of data:

1. Clear CF rules from range T:AB
2. Apply the following CF rule to range $T$7:$AB$20000 for the white-on-blue formatting =AND(ISEVEN(ROW()),T7<>"",SUMPRODUCT(--($D7:$Q7=T7),--($D8:$Q8=0)))
3. Apply the following CF rule to range $T$7:$AB$20000 for the white-on-red formatting =AND(ISODD(ROW()),T7<>"",SUMPRODUCT(--($D7:$Q7=T7),--($D8:$Q8=0)))
Note: change 20000 as needed
Click to expand...
Thank you Tetra201, for giving an alternate formula solution the trouble is a can not enter the formula receiving the following message </SPAN></SPAN>

"You may not use references to other worksheets or workbooks for Conditional Formatting criteria."</SPAN></SPAN>

I do not find way to apply the formula what I am doing wrong can not trace. please can you guide me</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>
 
Upvote 0
Hi, motilulla
Try this:

Code: 
[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1082697a()
[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]"TRUE"[/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, xlLogical).Font.Color = vbWhite
        .SpecialCells(xlConstants, xlLogical).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
Akuini said:
Hi, motilulla
Try this:
Click to expand...
Thank you Akuini, for building a VBA solution. I am getting error 1004 it stop at the line ".SpecialCells(xlConstants, xlLogical).Font.Color = vbWhite" and highlight it here is the results. Please can you check it? </SPAN></SPAN>


Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAAB
1
2
3
4
5n1n2n3n4n5n6n7n8n9n10n11n12n13n14CF1CF2CF3CF4CF5CF6CF7CF8CF9
6
701000111210101#N/A#N/A#N/A
800100000000210TRUE1TRUE
910504021010020#N/A#N/A#N/A#N/A#N/A
1021000132101100TRUETRUETRUE3
110131207012016210#N/A1#N/A37#N/A16
1202111040002010
Sheet1


Kind Regards,</SPAN></SPAN>
Moti</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,827
Messages
6,181,194
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