Scrip_Tips

[scrip-tips]

#Excel #VBA: Conditional Formatting Dynamic Selection

Using VBA driven Conditional Formatting for Dynamic Selection to reduce eye strain and parallax error when analyzing large datasets. The code preserves any existing regular formatting (non-conditional).

Note:

The following code is preferred to be inserted on a particular worksheet. instead of a module.

Also, this code in its current state can be used on a sheet that already does not have conditional formatting since it deletes and applies the 4 conditional rules on each selection change.

The color codes of for cell fill and font for the active selection range and the passive grid guiding range can be customized to desired color.

#Excel: Using #VBA driven #Conditional #Formatting for #Dynamic #Selection to reduce...Ref: https://t.co/pfiV7imRQv pic.twitter.com/BJP5cdDCdE

— Scrip_Tips (@Scrip_Tips) November 13, 2015

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'Macro to dynamically highlight cells to reduce parallax error and eye strain using conditional formatting Dim rng As Variant Cells.FormatConditions.Delete 'To clear any existing conditional formats

'Set the range (active selection) to apply the conditional format with a higher contrast theme to standout Set rng = Target

rng.FormatConditions.Add Type:=xlTextString, String:="*", TextOperator:=xlContains With rng.FormatConditions(1).Interior .ThemeColor = xlThemeColorLight2 .TintAndShade = 0.399975585192419 End With With rng.FormatConditions(1).Font .ThemeColor = xlThemeColorDark1 .TintAndShade = 0 End With rng.FormatConditions(1).StopIfTrue = False

rng.FormatConditions.Add Type:=xlTextString, String:="*", TextOperator:=xlDoesNotContain With rng.FormatConditions(2).Interior .ThemeColor = xlThemeColorLight2 .TintAndShade = 0.399975585192419 End With With rng.FormatConditions(2).Font .ThemeColor = xlThemeColorDark1 .TintAndShade = 0 End With rng.FormatConditions(2).StopIfTrue = False

'Set the remaining range (passive grid guiders) to apply the conditional format with a lesser contrast theme Set rng = Range(Target.EntireRow.Address & "," & Target.EntireColumn.Address)

rng.FormatConditions.Add Type:=xlTextString, String:="*", TextOperator:=xlContains With rng.FormatConditions(3).Interior .ThemeColor = xlThemeColorLight2 .TintAndShade = 0.799981688894314 End With rng.FormatConditions(3).StopIfTrue = False

rng.FormatConditions.Add Type:=xlTextString, String:="*", TextOperator:=xlDoesNotContain With rng.FormatConditions(4).Interior .ThemeColor = xlThemeColorLight2 .TintAndShade = 0.799981688894314 End With rng.FormatConditions(4).StopIfTrue = False

End Sub

[scrip-tips]

#Excel: Dynamic and Fail-Safe Hyperlinks to navigate within Workbook

When workbooks are open from a Network or SharePoint server, chances are that in-workbook links get broken. The reason for this failure is that, the hyperlink looks at the entire remote server path as opposed to the relative local reference to the workbook and thinks it as a different instance of the same referenced workbook.

i.e.

\\remote-server\[Example.xlsx]Sheet1

instead of,

[Example.xlsx]Sheet1

This can be over come by the use of HYPERLINK, CONCATENATE, CELL, FIND and MID functions to create dynamic and fail-safe link.

Example:

In continuation, dynamic and fail safe navigation links can be created. In this example a desired Sheet name to navigate to is given as a parameter in cell A2 and in cell B2 a dynamic link is created to point to Sheet2 A1 address (Figure 1).

Figure 1

Formula:

The formula is given below and an evaluation of the formula at a penultimate step is also shown (Figure 2).

=HYPERLINK(CONCATENATE(MID(CELL("filename"),FIND("[",CELL("filename"),1),FIND("]",CELL("filename"),1)-FIND("[",CELL("filename"),1)+1),"'",A2,"'!A1"),A2)

Figure 2

Hope this helps in some ways and see you all next time in another tip.

[scrip-tips]

[scrip-tips]

In Excel, we may had luck with not having to type all the months or days of the week in rows or columns for needs such as pivoting, reporting, etc. Since we can type just one month or the day in a...

[scrip-tips]

#Excel: Autofilling with Alphabets or other Custom lists or series

In Excel, we may had luck with not having to type all the months or days of the week in rows or columns for needs such as pivoting, reporting, etc. Since we can type just one month or the day in a cell(s) (Figure 1) and simply drag autofilling the adjacent cell values (Figure 2).

 Figure 1

Figure 2

Sometimes, we need alphabet series for custom personal orbusiness needs. Even if we type A In a single cell or A, B in two adjacent cellsand dragging cell values will result in all A (Figure 3)or repeating A, B values (Figure4)respectively.

 Figure 3

Figure 4

To overcome this limitation, type in A,B,C,…Z in adjacent cells just one time prior to adding to the custom list. If this is a daunting task, we can make use of the CHAR() function and a little knowledge on ANSI character set. Uppercase A is represented in ANSI by the value 65 in the Windows environment.

 Type in 65, 66 in two adjacent cells in a row wise (vertical) direction or column wise (horizontal) direction. For our purpose, we will go row wise as it is easy to deal with large members and small row height than large column width that is default. In the column adjacent to the cells containing 65 and 66, type “=CHAR()” and inside the parentheses click or point to the cell containing the value 65 and copy this formula to the next row corresponding to the cell containing the value 66. Select the range as shown below (Figure 5) and drag the cell value and formula range to autofill the rows below until we get Z corresponding to the value of 90 (65+25) (Figure 6).

Figure 5

Figure 6

Now we have the necessary alphabet series to be added to the custom list. Before we do that, the formula generated alphabet values need to be converted to just values by copying the range as pasting as values else there will be an error during import (Figure 7).

Figure 7

In Excel 2007, click the office button on the top left corner (Figure 8) and select “Excel Options” (Figure 9).

Figure 8

Figure 9

In the “Excel Options” window (Figure 10), in the “Popular” section select “Edit Custom Lists…” (Figure 11).

Figure 10

Figure 11

Select the “Option” range selector to select the range containing the alphabets (Figure 12) and click “Import” and after verifying the alphabet series is updated click ok (Figure 13).

Figure 12

Figure 13

Now you have at your disposal to create the alphabet series in Uppercase (Figure 14) and also don’t have to create a separate list for Lowercase (Figure 15) as it will be automatically recognized.

Figure 14

Figure 15

The above steps can be used to add other custom series such as Roman Numerals but without the leverage of CHAR() function. Hope this helps in some ways and see you all next time in another tip.