tecivo BLOG

My IT notes, 'How To...' guides, Microsoft, SharePoint, Citrix, AppSense, Apple, technology & gadgets and more!

Tag: Conditional Formatting

Excel Tips…Use Conditional Formatting with VLOOKUP to colour cell when match found

Introduction

Using VLOOKUP, NOT & ISNA in a Conditional Formatting rule is a really useful way to show whether a cell’s contents appear in a second data set.

To highlight Sheet1 Column A cells that appear in Sheet2 Column A, select Sheet1 Column A and create a new Conditional Formatting rule using ‘Use a formula to determine which cells to format‘ and use formula:

=NOT(ISNA(VLOOKUP(A1,Sheet2!$A:$A,1,FALSE)))

Sheet1

A B
1 Ford
2 Lotus
3 BMW
4 Audi
5 Mini
6 Ford

Sheet2

A B C D
1 Ford US Escort Red
2 Lotus UK Elise Silver
3 BMW Germany 330 Blue
4 VW Germany Golf Black
5 Ford US Mondeo Silver
6 Mini Cooper

To apply format rule to show non-matches use

=ISNA(VLOOKUP(A1,Sheet2!$A:$A,1,FALSE))
A B
1 Ford
2 Lotus
3 BMW
4 Audi
5 Mini
6 Ford

Also see

 

SharePoint Tips…How to apply colour formatting to a List item column based on value in other column

Introduction

A useful trick with SharePoint lists is to use colour coding so you can quickly see the status of the item.

For example you may have a calendar list to which you’ve added a Status column using a choice for Complete, Started, Pending.

————

The example below shows how to create a new column which will show colours for each status – Complete=Green, Started=Orange and Pending=Red

Create a new column StatusColour in the SharePoint list using Calculated column type and enter formula as appropriate using data type Number

To change background colour:

="<div style='text-align:left; background-color:"&IF(Status="Complete","green",IF(Status="Started","orange",IF(Status="Pending","red")))&"'>"&Status&"</div>"
Title Status StatusColour < ‘StatusColour’ function >
Job 1 Complete Complete =”<div style=’text-align:left; background-color:”&IF(Status=”Complete”,”green”,IF(Status=”Started”,”orange”,IF(Status=”Pending”,”red”)))&”‘>”&Status&”</div>”
Job 2 Started Started =”<div style=’text-align:left; background-color:”&IF(Status=”Complete”,”green”,IF(Status=”Started”,”orange”,IF(Status=”Pending”,”red”)))&”‘>”&Status&”</div>”
Job 3 Pending Pending =”<div style=’text-align:left; background-color:”&IF(Status=”Complete”,”green”,IF(Status=”Started”,”orange”,IF(Status=”Pending”,”red”)))&”‘>”&Status&”</div>”

To change font colour:

="<div style='text-align:left; color:"&IF(Status="Complete","green",IF(Status="Started","orange",IF(Status="Pending","red")))&"'>"&Status&"</div>"
Title Status StatusColour < ‘StatusColour’ function >
Job 1 Complete Complete =”<div style=’text-align:left; background-color:”&IF(Status=”Complete”,”green”,IF(Status=”Started”,”orange”,IF(Status=”Pending”,”red”)))&”‘>”&Status&”</div>”
Job 2 Started Started =”<div style=’text-align:left; background-color:”&IF(Status=”Complete”,”green”,IF(Status=”Started”,”orange”,IF(Status=”Pending”,”red”)))&”‘>”&Status&”</div>”
Job 3 Pending Pending =”<div style=’text-align:left; background-color:”&IF(Status=”Complete”,”green”,IF(Status=”Started”,”orange”,IF(Status=”Pending”,”red”)))&”‘>”&Status&”</div>”

To change font and background colours:

="<div style='text-align:left; background-color:"&IF(Status="Completed","green",IF(Status="Started","orange",IF(Status="Pending","red")))&"; color:"&IF(Status="Complete","green",IF(Status="Started","orange",IF(Status="Pending","red")))&"'>"&Status&"</div>"
Title Status StatusColour < ‘StatusColour’ function >
Job 1 Complete Complete =”<div style=’text-align:left; background-color:”&IF(Status=”Complete”,”green”,IF(Status=”Started”,”orange”,IF(Status=”Pending”,”red”)))&”‘>”&Status&”</div>”
Job 2 Started Started =”<div style=’text-align:left; background-color:”&IF(Status=”Complete”,”green”,IF(Status=”Started”,”orange”,IF(Status=”Pending”,”red”)))&”‘>”&Status&”</div>”
Job 3 Pending Pending =”<div style=’text-align:left; background-color:”&IF(Status=”Complete”,”green”,IF(Status=”Started”,”orange”,IF(Status=”Pending”,”red”)))&”‘>”&Status&”</div>”

Note: When using a column with spaces in the name you need to wrap in square brackets e.g. Current Status should be defined in IF as [Current Status] and at end of function as &[Current Status]&

You can use HTML colour codes as well as names.

e.g. set background and font colours based on Status column:

="<div style='text-align:left; background-color:"&IF(Status="Complete","#008000",IF(Status="Started","#800080",IF(Status="Pending","#FF0000")))&"; color:"&IF(Status="Complete","white",IF(Status="Started","white",IF(Status="Pending","white")))&"'>"&Status&"</div>"

 

© 2017 tecivo BLOG

Theme by Anders NorenUp ↑