SOLVED

Spaltenvergleich größer, kleiner

%3CLINGO-SUB%20id%3D%22lingo-sub-1962715%22%20slang%3D%22de-DE%22%3EColumn%20comparison%20larger%2C%20smaller%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1962715%22%20slang%3D%22de-DE%22%3E%3CP%3EHi%2C%20I'm%20desperately%20looking%20online%2C%20but%20I%20can't%20solve%20my%20problem%20yet!%3C%2FP%3E%3CP%3EI%20have%202%20columns%20(A%2CB)%20and%20I%20want%20to%20select%20the%20B-values%20if%20they%20are%20larger%20or%20smaller%20than%20the%20A-values%20(red%20or%20green).%20Example%3A%20B5%20is%20larger%20A5%20-%26gt%3B%20B5%20red%2C%20B9%20is%20smaller%20A9%20-%26gt%3B%20B9%20is%20green.%20Conditional%20formatting%20seems%20to%20me%20to%20be%20the%20right%20way%20to%20go.%20Here%20I%20can%20enter%20the%20rules%20but%20so%20far%20only%20cell%20by%20cell.%20Is%20there%20also%20a%20way%20to%20do%20this%20for%20marked%20areas%3F%20After%20all%2C%20there%20are%20hundreds%20of%20cells%20and%20if%20I%20have%20to%20specify%20two%20rules%20per%20cell%2C%20I'm%20not%20ready%20next%20week.%3C%2FP%3E%3CP%3ECan%20someone%20help%20me%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1962715%22%20slang%3D%22de-DE%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1962792%22%20slang%3D%22en-US%22%3ERe%3A%20Spaltenvergleich%20gr%C3%B6%C3%9Fer%2C%20kleiner%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1962792%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F893426%22%20target%3D%22_blank%22%3E%40FuxLoyd%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20do%20this%20with%20just%20two%20rules%20for%20the%20entire%20range.%3C%2FP%3E%0A%3CP%3ELet's%20say%20the%20data%20start%20in%20row%202.%3C%2FP%3E%0A%3CP%3ESelect%20the%20range%20from%20B2%20to%20the%20last%20used%20row.%3C%2FP%3E%0A%3CP%3EI%20will%20assume%20that%20B2%20is%20the%20active%20cell%20within%20the%20selection.%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EOn%20the%20Home%20tab%20of%20the%20ribbon%2C%20select%20Conditional%20Formatting%20%26gt%3B%20New%20Rule...%3C%2FLI%3E%0A%3CLI%3ESelect%20'Format%20only%20cells%20that%20contain'.%3C%2FLI%3E%0A%3CLI%3ELeave%20the%20first%20dropdown%20set%20to%20'Cell%20Value'.%3C%2FLI%3E%0A%3CLI%3ESelect%20'greater%20than'%20from%20the%20second%20dropdown.%3C%2FLI%3E%0A%3CLI%3EEnter%20the%20formula%20%3DA2%20in%20the%20box%20next%20to%20it.%20Remember%2C%202%20is%20the%20row%20of%20the%20active%20cell%3C%2FLI%3E%0A%3CLI%3EClick%20Format...%3C%2FLI%3E%0A%3CLI%3EActivate%20the%20Fill%20tab.%3C%2FLI%3E%0A%3CLI%3ESelect%20red.%3C%2FLI%3E%0A%3CLI%3EClick%20OK%2C%20then%20click%20OK%20again.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3ERepeat%20the%20above%20steps%2C%20but%20with%20'less%20than'%20instead%20of%20'greater%20than'%20and%20with%20green%20instead%20of%20red.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1962849%22%20slang%3D%22de-DE%22%3ERe%3A%20Column%20comparison%20larger%2C%20smaller%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1962849%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3Evogelaar%20%40Hans%3C%2FA%3E%26nbsp%3Btop!%20It%20works.%20Thank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi, ich suche schon verzweifelt im Netz, kann mein Problem aber bisher nicht lösen!

Ich habe 2 Spalten (A,B) und möchte jeweils die B-Werte markieren, wenn sie größer oder kleiner den A-Werten sind (rot bzw. grün). Beispiel: B5 ist größer A5 -> B5 rot, B9 ist kleiner A9 -> B9 grün. Bedingte Formatierung scheint mir der richtige Weg. Hier kann ich die Regeln aber bisher leider nur Zelle für Zelle eingeben. Gibt es auch eine Möglichkeit, dies für markierte Bereiche zu tun? Es sind schließlich hunderte Zellen und wenn ich da jeweils pro Zelle zwei Regeln angeben muss, bin ich nächste Woche noch nicht fertig.

Kann mir jemand helfen?

3 Replies
best response confirmed by FuxLoyd (New Contributor)
Solution

@FuxLoyd 

You can do this with just two rules for the entire range.

Let's say the data start in row 2.

Select the range from B2 to the last used row.

I will assume that B2 is the active cell within the selection.

  • On the Home tab of the ribbon, select Conditional Formatting > New Rule...
  • Select 'Format only cells that contain'.
  • Leave the first dropdown set to 'Cell Value'.
  • Select 'greater than' from the second dropdown.
  • Enter the formula =A2 in the box next to it. Remember, 2 is the row of the active cell
  • Click Format...
  • Activate the Fill tab.
  • Select red.
  • Click OK, then click OK again.

Repeat the above steps, but with 'less than' instead of 'greater than' and with green instead of red.

@Hans Vogelaar Top! Es funktioniert. Ich danke dir!

@Hans Vogelaar Thanks for the description, it worked very well and saved my Sunday