microsoft office 2007 Ultimate code Modifying Cond

[pingh85]

Today’s authors, Amit Velingkar,buy microsoft office 2010 generator key, a Program Manager on the Excel team, and Bob Silverstein, a User Experience Researcher on the Excel team, talk about creating professional looking Color Ranges in Excel. fellow PM on Excel,microsoft office 2007 Ultimate code, Robin Wakefield, wrote a blog article on how to create professional looking charts in Excel 2007. Continuing with the same theme, we will investigate the idea of tweaking Color Ranges in Excel 2007. We will start with a default Conditional Formatting Color Range and modify it to give it more pastel look. Also, this would be an ideal forum to give us your feedback on existing conditional formatting colors used in Excel 2007. a part of my preparation for this article, I did two things – researched customer files that use conditional formatting and explored various options with Microsoft designers. at some customer files, I quickly observed a trend. I found out that many users wanted to highlight a small portion of their data. They were achieving this by changing some of the colors in their color ranges to white. Also, some customers preferred a pastel shade of the same color. this trend with Microsoft designers and they taught me some neat tricks about manipulating colors. First, they told me that choosing the correct shade of white was critical while mixing colors. And last, an intuitive way of changing shades of colors was to use the HSL (Hue, Saturation and Luminosity) model. blog article attempts to bring all these lessons together. We will change some of the colors to white and tweak the colors shades to pastel. Finally, we will introduce a VBA macro that lets you quickly try out new color shades. I started out with applying three of our default color combinations to some data. I choose the following color ranges: Red-Yellow-Green, Red-Yellow-Blue and Yellow-Green. I replaced the yellow color with the appropriate shades of “white”. the yellow colors to pure whites created a high contrast. For creating whites with a good spectrum of colors, we crank up the RGB values all the way to 255 creating a pure white – Remember RGB (255,255,255) is white. Then, remove just a little amount of the participating color to create “target” white. an example while mixing Red and Green, we set our white to (250,250,255) by removing some red and green. The end result is a color range containing pure shades of Red and Green.
Color 1
Color 2
White
Notes
Red (255,0,0)
Green (0,255,0)
(250,250,255) some red and green
Red (255,0,0)
Blue (0,0,255)
(250,255,250) some red and blue
Red(255,0,0)
-
(250,255,255) some red
Green (0,255,0)
-
(255,250,win 7 pro product key,255)
Remove some green Select the color range. On the Home tab, click on Conditional Formatting, then Manage Rules. In the Conditional Formatting Rules Manager, select the Conditional Format and click on Edit Rule. In the Edit Formatting Rule dialog change the yellow color to white.
Using HSL Model to change non-white colors use the HSL model to manipulate color. HSL defines the color spectrum based on Hue (base color from the rainbow spectrum), Luminosity (brightness) and Saturation (purity of the color). the color range. On the Home tab, click on Conditional Formatting, then Manage Rules. In the Conditional Formatting Rules Manager, select the Conditional Format and click on Edit Rule. In the Edit Formatting Rule dialog, click on the color dropdown and choose More Colors. Click on the Custom tab and choose HSL in the Color model dropdown. this example, we will change colors using the HSL models as follows: Hue: Unchanged, this leaves the base color unchanged. Hence our Reds stay Red. Saturation: Decrease the Saturation to 150. This makes the colors more pastel. Luminosity: Increase the luminosity to 200. The goal is to increase the brightness to make the color more visible and showcase the text behind the color ranges. shades of white and by tweaking the luminosity and saturation of colors, we changed the color ranges as follows: Manipulation using VBA: I wanted to try out a lot of combinations of Luminosity and Saturation values to tweak color ranges, I decided to write a VBA macro to do this. I quickly learnt that while Excel allows HSL manipulation through the UI, it does not support HSL manipulation through VBA. I looked around for ways to manipulate HSL and finally found a blog post by Tony Jollans that did something similar. Although most of the code used is directly from Tony Jollans’s blog post, I wrote some additional subroutines, added some comments and re-factored some of the subroutines for my purpose. entry function for my macro is called ChangeColorRangeColors().
Sub ChangeColorRangeColors()
... For Each clrScale In Selection.FormatConditions
For Each criteria In clrScale.ColorScaleCriteria
' get clr
clr = criteria.FormatColor.Color ' split RGB
SplitRGB clr, R,microsoft office Home And Student 2010 code, G, B 'skip whites - when all RGB componenst are above 240
If R < 240 Or G < 240 Or B < 240 Then ' get HSL
RGBtoHSL clr, H, S, L ' increase luminosity
L = (iLuminosity / 255) ' from 0.0 to 1.0 ' decrease saturation
S = (iSaturation / 255) ' from 0.0 to 1.0 ' get RGB
HSLtoRGB H, S, L, clr ' set new clr
criteria.FormatColor.Color = clr End If
Next
Next
End Sub a high level, my HSL manipulator code works in the following ways: Get the RGB encoded value from the selected color ranges. Split the RGB encoded values into the Red, Green and Blue components. Skip the white colors – we do not want to change these. Covert RGB into HSL. Modify the Saturation and Luminosity, leaving Hue unchanged. Convert the modified HSL values back to encoded RGB. Apply the new RGB value back to the color range.
I won’t go into the details of converting between RGB and HSL values. These conversion routines are well documented and can be freely found on the internet. tricky part was trying to split the encoded color value that is used by Excel into individual RGB components. VBA provides a function, aptly named RGB(), that returns an encoded long value when provided with the R,microsoft office pro plus license,G and B components. I have used this function in my macro to set the Color Range colors. However, VBA does not provide a reverse function to split this encoded long value. I have used a custom function called SplitRGB to do this.
Sub SplitRGB(RGB As Long, R As Double, G As Double, B As Double)
Dim HexString As String ' convert the long to Hex - bb:gg:rr
HexString = Hex(RGB) ' in order to get r,g,b components out of the string,
' we have to make it is atleast 6 characters long - bb:gg:rr
HexString = Right(String$(5, "0") & HexString, 6) ' get each individual color and convert to an double (range: 0 to 255)
R = CDbl("&H" & Mid$(HexString, 5, 2))
G = CDbl("&H" & Mid$(HexString, 3, 2))
B = CDbl("&H" & Mid$(HexString, 1, 2))
End Sub the R, G, B values span from 0 to 255 and can be accommodated in 1 byte. Excel uses a 4-Byte long to encode the RGB values. The first byte is used to store the red value, the second byte stores the green value and the third byte stores the blue values. An easy way to observe these values is to convert this encoded long into a hexadecimal string. We then split this string using the Mid function to isolate the R, G, B components. Finally, we use a conveniently provided CDbl function to convert hexadecimal text to a number. Observe that appending the “&H” enables the CDbl function to identify the text as hexadecimal. recap, We can create professional looking Conditional Formatting color ranges by choosing the correct shades of whites and using the HSL model to tweak Saturation and Luminosity values. I am hoping that by using these techniques and subroutines, you will be able to automate the look and feel of your Excel Spreadsheets. attached workbook contains these color ranges and the VBA macros. We would love to hear your feedback on these colors. <div