Color scales in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to apply a 3-color scale conditional formatting in Excel.

Excel

Range("A1:A10").FormatConditions.AddColorScale([1])

Drag options to blanks, or click blank then click option'

A3

B2

C1

D4

Attempts:

3 left

💡 Hint

Common Mistakes

Using 2 instead of 3 for a 3-color scale.

Using 4 which is not valid for color scales.

Excel uses 3 for a 3-color scale in conditional formatting.

2fill in blank

medium

Complete the code to set the minimum color to red in a 3-color scale.

Excel

With Range("B1:B10").FormatConditions(1).ColorScaleCriteria(1)
 .Type = xlConditionValueLowestValue
 .FormatColor.Color = [1]
End With

Drag options to blanks, or click blank then click option'

ARGB(0, 0, 255)

BRGB(0, 255, 0)

CRGB(255, 0, 0)

DRGB(255, 255, 0)

Attempts:

3 left

💡 Hint

Common Mistakes

Using green or blue RGB values instead of red.

Using yellow which is not red.

Red color in RGB is (255, 0, 0), which sets the minimum color to red.

3fill in blank

hard

Fix the error in setting the midpoint color to yellow in a 3-color scale.

Excel

With Range("C1:C10").FormatConditions(1).ColorScaleCriteria(2)
 .Type = xlConditionValuePercentile
 .Value = 50
 .FormatColor.Color = [1]
End With

Drag options to blanks, or click blank then click option'

ARGB(255, 255, 0)

BRGB(0, 0, 255)

CRGB(0, 255, 0)

DRGB(255, 0, 0)

Attempts:

3 left

💡 Hint

Common Mistakes

Using red or green instead of yellow.

Using blue which is not yellow.

Yellow color is RGB(255, 255, 0), which is correct for midpoint color.

4fill in blank

hard

Fill both blanks to set the maximum color to green and the type to highest value.

Excel

With Range("D1:D10").FormatConditions(1).ColorScaleCriteria([1])
 .Type = [2]
 .FormatColor.Color = RGB(0, 255, 0)
End With

Drag options to blanks, or click blank then click option'

A3

BxlConditionValueHighestValue

CxlConditionValueLowestValue

D1

Attempts:

3 left

💡 Hint

Common Mistakes

Using index 1 or 2 instead of 3 for maximum color.

Using lowest value type instead of highest.

The maximum color is the third color in the scale, so index 3. The type for maximum is xlConditionValueHighestValue.

5fill in blank

hard

Fill all three blanks to create a 3-color scale with red minimum, yellow midpoint, and green maximum colors.

Excel

With Range("E1:E10").FormatConditions.AddColorScale([1])
 With .ColorScaleCriteria([2])
  .Type = xlConditionValueLowestValue
  .FormatColor.Color = RGB(255, 0, 0)
 End With
 With .ColorScaleCriteria([3])
  .Type = xlConditionValuePercentile
  .Value = 50
  .FormatColor.Color = RGB(255, 255, 0)
 End With

Drag options to blanks, or click blank then click option'

A3

B1

C2

DxlConditionValueHighestValue

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong indices for color criteria.

Using wrong number of colors in AddColorScale.

Use 3 for the number of colors. The minimum color is index 1, midpoint is index 2.