Bit odd conditional formatting

M

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,362
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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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 :biggrin:</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

K
  • Question
Conditional Formating - Dynamic "not between"
Replies
3
Views
292
dreid1011
dreid1011
R
  • Question
Excel Conditional Formatting Formula to change fill colour based on position of active cell
Replies
2
Views
590
rasinc
R
Nuke_It_Newport
  • Solved
Conditional Formatting Rules Not Applying
Replies
1
Views
694
Nuke_It_Newport
Nuke_It_Newport
nicolews
  • Question
Conditional Formatting based on other cell values
Replies
3
Views
843
ERINWILLIS01
E
M
  • Question
[Pls Help!]Grant chart project with conditional formatting
Replies
1
Views
290
etaf
etaf

Forum statistics

Threads
1,215,375
Messages
6,124,578
Members
449,174
Latest member
chandan4057

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