Conditional Formatting all numbers two colors

Re: Conditional Formatting all numbers two colors

Hi Larbec

It is not really a job for vba. Conditional formatting is your puppy of choice as this is what it was designed for. Self explanatory...

=IsODD(a2)

=Iseven(A2)

File attached to show workings.

TheSmallman

Re: Conditional Formatting all numbers two colors

Hi larbec

Read this Slow Response, Memory Problems, and Speeding up Excel

You do not want Conditional Formating (CF) for an entire sheet as it will slow your workbook down to agrinding halt !
Limit CF to the mimimum. better still aviod it.

Re: Conditional Formatting all numbers two colors

Actually, only the CF in the visible scroll range are evaluated so it doesn't necessarily bog things down if a large area of CF is used.

Re: Conditional Formatting all numbers two colors

Copy a cell with the formatting .. then Click in the Grey square between A column and 1 row headers
this square highlights the entrie worksheet
Then Right click and only paste formatting

Re: Conditional Formatting all numbers two colors

This code added to each sheet where you want the formatting to apply will conditionally format any cell on the sheet that is either manually changed or changed using copy/paste. It is possible to paste a range of cells and the formatting will be applied to all.

I have restricted the change that will trigger the formatting to a number between 1 and 9 inclusive, assuming that you only need the conditional formatting applied to those values, as in your data set.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCel As Range
Dim sAdd As String
For Each rCel In Target
    sAdd = rCel.Address
    If rCel >= 1 And rCel <= 9 Then
        With rCel
            .FormatConditions.Delete
            .FormatConditions.Add Type:=xlExpression, Formula1:="=ISODD(" & sAdd & ")"
            With rCel.FormatConditions(1)
                .Font.Color = -16776961
                .Interior.Color = 65535
            End With
            .FormatConditions.Add Type:=xlExpression, Formula1:="=ISEVEN(" & sAdd & ")"
            With rCel.FormatConditions(2)
                .Font.Color = -11489280
                With .Interior
                    .ThemeColor = xlThemeColorDark1
                    .TintAndShade = -0.249946592608417
                End With
            End With
        End With
    Else
        rCel.FormatConditions.Delete
    End If
Next
End Sub

Hope this helps

Re: Conditional Formatting all numbers two colors

Try this in a Worksheet Selection Change Event. As the other members pointed out it may slow your workbook down.

Dim x As Integer


For Each c In ActiveSheet.UsedRange.Cells


         If c = "" Then c.Interior.Color = xlNone
         
           If Application.WorksheetFunction.IsText(c) Then
             c.Font.ColorIndex = 1
               c.Interior.Color = xlNone
GoTo NC
           End If




   x = c.Value




         If ((x Mod 2) = 0) Then
           c.Font.Color = vbGreen
          Else
           c.Font.Color = vbRed
           c.Interior.Color = vbYellow
         End If
    
    
NC:
Next c