Free Microsoft Excel 2013 Quick Reference

Conditional formatting with 2 variables Results

Hi there,

Just trying a bit of simple conditional formatting but have come unstuck.

I have 2 columns, one with a date and one with a status (open/closed).

I'd like the Date column to show a different font colour dependent on both the date AND the status.

e.g. If today is the 11-Feb:

Column A-----Column B
--09-Feb------Closed-- should show Green
--11-Feb------Open-- should show Orange
--10-Feb------Open-- should show Red
--10-Feb------Closed-- should show Green

Can this be done using Conditional Formatting? I'd rather not use VBA if possible.

Hi,
I am new to the forum and from what I have read, there are some great people out there willing to help and I want to say from the beginning I appreciate your time and effort.

I am trying to use conditional formatting but it needs to be based on 2 factors because some of the values overlap depending on the material being made.
A2 is a drop down containing text for the 4 materials
A3 is manually populated
A4 is where conditional formatting is to be applied based on the value in A3
The same applies to B3 & B4, A5 & A6, B5 & B6
G3:L3 are the values used for the formatting a, b, c, d, for bio, the following 3 rows are for the other 3 materials made. I have attached a file for reference, there is no conditional formatting in it.

I have a problem that I've been trying to solve the last 2 days without results.

I need to add the period in which an employee will be working in a graph ( see Calendar Tab).

In the sheet 1, every employees will have a task assigned and a period. The task will be a drop down list (I only use 1,2 or 3 for now) and each task will have a different colour. Also, the period will be entered manually (for month to month).

For every task and period entered, I need the fields in the sheet Calendar to be automatically filled giving the criteria. For example, if employee xxx1 is assigned to task 1(let's say it's red) from May 2012 to December 2012, in the Calendar tab, a red line should appear automatically next to his name from May 2012 to December 2012.

Anyone as a solution for me?

Regards!

Need to created a Conditional formatting for the following:

Cells in rows J, R and Z contain data that needs the condition applied to.

Want to format the above cells with the following criteria:
The below is the criteria based on what percentage these values are off of the average value of these 3 cells
-20% to 20% Green
-30% to -20% and 20% to 30% Yellow
Less than -30% and Greater than 30% Red

I am able to perform this conditional formatting on a cell by cell basis with the following:
Condition 1:
Cell Value is Between -1.2*AC2 and 1.2*AC2 is Green
Cell Value is Between -1.2*AC2 and -1.3*AC2 is Yellow
Cell Value is Between 1.2*AC2 and 1.3*AC2 is Yellow
Cell Value is Greater than -1.3*AC2 is Red
Cell Value is Greater than 1.3*AC2 is Red

I want to see if there is a more effective way to do this against all the cells in rows J, R, and Z referenced against the average of the cells in row J, R, and Z data in all the cells in row AC

Example:
Row J Row R Row Z Row AC
12 16 8 12
Green Red Red

Let me know if you need further information and I appreciate any assistance or references you may be able to provide.

Submitted via EggHeadCafe - Software Developer Portal of Choice
How to Get Your Silverlight Pages Indexed By Search Engines
http://www.eggheadcafe.com/tutorials...silverlig.aspx

Hello,

The attached file is an MS Access export. I am running into a snag where I want to add conditional formatting to a cells in a row based on cells in 2 other rows above being unequal (). The trick is the cells for the formula can differ on every run and are created as variables as part of the code.

The attached workbook shows what the workbook looks like before and after the code is run (see code below-none in workbook) as well as where I am stuck.

Thanks for any help...greatly appreciated!


	VB:
	
 
Sub BalancesforInvPayment() 
     '
     ' Review Payment Balances query export formatting
    Dim LastCol As Long 
    Dim LastRow As Long 
    Dim TotalRow As Long 
    Dim InvVoucher As Long 
    Dim BalRange As Long 
    Application.ScreenUpdating = False 
     
     'set formulas and conditonal formatting
    Range("D2").Select 
    Range(Selection, Selection.End(xlToRight)).Select 
    Range(Selection, Selection.End(xlDown)).Select 
    Selection.FormatConditions.Delete 
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ 
    "=IF(SUM($D2:$E2)=$C2,TRUE,)" 
    With Selection.FormatConditions(2).Font 
        .Bold = True 
        .Italic = False 
    End With 
    Selection.FormatConditions(2).Interior.ColorIndex = 3 
     ' calulate range, set variables
    With ActiveSheet 
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row 
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column ' changed .Cells(4, to 1,...
        TotalRow = LastRow + 1 
        InvVoucher = LastRow + 3 
        BalRange = LastRow + 4 
         
         'set lastrow plus "X" for totals row
         'set starting column, then "resize" area where formulas will go _
        by subtracting starting column from total number of columns...work on changing To a variable 
        Cells(TotalRow, "C").Resize(1, LastCol - 2).Select 
        With Selection.Borders(xlEdgeTop) 
            .LineStyle = xlDouble 
            .Weight = xlThick 
            .ColorIndex = xlAutomatic 
        End With 
         
         'in formula set R[- "y"] to row where sum stosp relative to totals row
        Cells(TotalRow, "C").Resize(1, LastCol - 2).FormulaR1C1 = "=sum(R2C:R[-1]C)" 
         
         'format the last row to bold
        Cells(TotalRow, "C").Resize(1, LastCol - 2).Font.Bold = True 
         'Cells(.Rows.Count, 1).End(xlUp).Resize(1, LastCol).Font.Bold = True
         
         'set target voucher cells
        Cells(InvVoucher, "D").Resize(1, LastCol - 3).Select 
        With Selection.Borders 
            .LineStyle = xlContinuous 
            .Weight = xlMedium 
            .ColorIndex = xlAutomatic 
        End With 
        With Selection.Borders(xlInsideVertical) 
            .LineStyle = xlNone 
        End With 
        With Selection.Borders(xlInsideHorizontal) 
            .LineStyle = xlNone 
        End With 
         
         
        Cells(TotalRow, "C").Value = "TOTALS" 
        Cells(InvVoucher, "C").Value = "Enter Invoice Target $" 
        Cells(InvVoucher, "C").Select 
        Selection.Font.Bold = True 
        Selection.HorizontalAlignment = xlRight 
        Cells(BalRange, "C").Value = "Balance" 
        Cells(BalRange, "C").Select 
        Selection.Font.Bold = True 
        Selection.HorizontalAlignment = xlRight 
         
         
         
         ' Need this section to format based on comparison that cells in same column above in TOTALS row and INVVOUCHER row
are 
        Cells(BalRange, "D").Resize(1, LastCol - 3).Select 
        With Selection 
            .FormatConditions.Delete 
             '            .FormatConditions.Add Type:=xlExpression, Formula1:= _
             'need formula here                "=IF(   TRIT,       TRUE,)"
             '            .FormatConditions(1).Interior.ColorIndex = 3
        End With 
    End With 
    Application.ScreenUpdating = True 
    Range("A2").Select 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi,

I have created a gantt chart that fills all cells between certain dates with blue using the conditional formatting formula, =IF(AND(A$5>=$B3, A$5

Mockup of spreadsheet:

Task Number . Current step . 11/1 . 11/2 . 11/3 . 11/4 . 11/5
Task 1
Task 2
Task 3
Task 4

The rows of my spreadsheet represent tasks to be done, each of which requires completion of a series of steps. The first column represents the current status of each task (what step you're on). After that there's a bunch of columns with date headers, which is used as a planning calendar - for each task, you fill in the date columns with the planned step for that date.

What we'd like to do is set up a conditional formatting that colors the cells in the "current step" column. If the current step is on track with the step planned for today's date, the "current step" cell would turn green. If the current step is behind the planned step for today's date, the cell would be red.

But that turns out to be tricky since the exact cell driving the format rule would change every day. Can anyone think of a way to do this? Hopefully something robust enough that it won't break every time someone messes around with the spreadsheet?

I have a situation which simple INDIRECT will not solve. Workflow:

1. User chooses a category in column A out of ten choices.
2. Three of those choices cause columns B-F to pre-populate with default entries (different for each of the three categories).
3. The other seven category choices in column A cause columns B-F to black out.
4. The user can change the default entries, but I would like them to only be able to choose from a defined set of entries based on the category they choose in Column A.

I have figured out steps 1 and 2. For step 3, I know how to do condition formatting to cause a cell to black out, but not necessarily in conjunction with all these other variables. For step 4, I know how to do conditional lists using INDIRECT when there are only two lists, but I can't figure out how to get that method to work here with all these moving parts.

Thank you for your help!

I apologize...I tabbed to the submit button without realizing it and submitted before I was finished...

Hello everyone! I am just a beginner to VBA and forums, so I am still learning how to optimize code and get help. The trouble I'm having right now is that I have a user form with many checkboxes that populates a spreadsheet dynamically, then either writes formulas or applies conditional formatting to a range of cells to the right. The problem is that I want to apply the same conditional formatting to various cells across the range and am rewriting the code for this for every cell. And because the cells vary, depending on the product, I can't seem to figure out how to write the format code only once and then refer to it every time I need it. Here is an example:

	VB:
	
 StartCell 
    .Value = Ctl.Tag 
    With .Offset(0, 1) 
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula:="=""""" 
        .FormatConditions(1).Interior.ColorIndex = 15 
    End With 
    With .Offset(0, 2) 
        (repeat same As above) 
    End With 
    With .Offset(0, 5) 
        (repeat same As above) 
    End With 
    ... 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
So what I would like to do is set some kind of variable that holds the conditional format so that I can refer to it several times not only in the one sub, but in the other subs that I am using, as well (all in the same userform module). OR to group those .offset cells - ie:


	VB:
	
 .offset(0, 5) And... 
    (conditional format code here) 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I know that code doesn't work, but maybe there's something similar?? So I hope that makes sense and that someone can help. The code I'm running now works fine, but it's just a lot of repeated code that I feel I should be able to condense. Thank you!!

As is probably obvious, I'm quite new to VBA (and not very good at it I suspect) however, I'm trying to construct a way to run some conditional formatting, such that row 2 = IF D2 "" Then compare grades to that value, otherwise it should follow other rules (over C is green, etc). So I wrote what I hoped would act as a kind of Vlookup, but without requiring me to place the numeric translation into the worksheets themselves, to compare those values.
Below that, the next row has numeric values, and should carry out formatting on those according to similar rules (if over 7, then green). However, the way I've written this code seems to lead to variable errors and such...if anyone has any suggestion they'd be much appreciated. Ive posted the ful relevant code below - sorry it's long

Code:
  Dim Grade As Long
 'for the range of used cells, with 'lookup' below
 Dim Width As Long
 Width = Range("Dates").Columns.Count
 For Each g In Range("$D" & i, "$2" & Width).Cells
 If g.Value = "*" Then
 Grade = 58
 If g.Valuee = "A" Then
 Grade = 52
 If g.Value = "B" Then
 Grade = 46
 If g.Value = "C" Then
 Grade = 40
 If g.Value = "D" Then
 Grade = 34
 If g.Value = "E" Then
 Grade = 28
 If g.Value = "F" Then
 Grade = 22
 If g.Value = "G" Then
 Grade = 16
 If g.Value = "U" Then
 Grade = 1
 If g.Value = "x" Then
 Grade = 0
 If g.Value = "Dist" Then
 Grade = 58
 If g.Value = "Mer" Then
 Grade = 52
 If g.Value = "Pass" Then
 Grade = 46
 If g.Value = "L1" Then
 Grade = 25
 If g.Value = "L2" Then
 Grade = 46
 If g.Value = "L3" Then
 Grade = 58
 Else: Grade = ""
 End If
 End If
 End If
 End If
 End If
 End If
 End If
 End If
 End If
 End If
 End If
 End If
 End If
 End If
 End If
 End If
 Next
 With Range("E2").Resize("1", "1" & Columns.Count) 'with the first pair of Grade then Motivation to the end of dates
 For Each l In Range("E2", "E2" & .Offset(0, "$1" & Columns.Count)).Cells
 If Grade < D2 Then
 Target.Interior.ColorIndex = 3
 If Grade > D2 Then
 Target.Interior.ColorIndex = 5
 If Grade = D2 Then Target.Interior.ColorIndex = 4
 End If
 End If
 Next
 If D2 = "" Then
 For Each m In Range("E2", "E2" & .Offset(0, "$1" & Columns.Count)).Cells
 If Grade < 40 Then
 Target.Interior.ColorIndex = 3
 If Grade > 40 Then
 Target.Interior.ColorIndex = 5
 If Grade = 40 Then
 Target.Interior.ColourIndex = 4
 Else: Target.Interior.ColorIndex = 24
 End If
 End If
 End If
 Next
 With Range("E3").Resize("1", "1" & Columns.Count) 'first motivation one
 For Each h In Range("E3", "E3" & .Offset(0, "$1" & Columns.Count)).Cells
 If h.Value < 5 Then
 Target.Interior.ColorIndex = 3
 If h.Value >= 7 Then
 Target.Interior.ColorIndex = 4
 If h.Value < 7 Then
 Target.Interior.ColorIndex = 6
 Else: Target.Interior.ColorIndex = 19
 End If
 End If
 End If
 Next
 End With
End If
 End With
End Sub


I am running into an issue that I just don't understand.

Basically, I want to change the value of a textbox when the value of another one changed. The value that I want to change it to depends on two things. 1) if the variable temp is located in Col H in a different workbook and 2) the value of variable pp_results.

When I run the code if temp is located in the defects workbook then the code runs great. However if it is not then I get a run time error as follows
" Run-Time error '91':
Object Variable or With Block variable not set"

It will error out on the first if statement. When I check the values of the variables they are correct. d=nothing like it is supposed to be.

Here is the code that I am running

Code:
Private Sub pp_results_change()
Set ws2 = Worksheets("Defects")
temp = Serial_Number_form.Serial_number.Value
d = ""
With ws2.Range("H1:H9999")
    Set d = .Find(temp, LookIn:=xlValues, lookat:=xlWhole)
     
End With
 
'it errors on the next line
If d = "" Or d Is Nothing Then
    If PP_results.Value = "Pass" Then
        First_Pass.Value = "Passed"
        First_Pass.BackColor = &HFF00&
        First_Pass.ForeColor = &HFFFFFF
    Else
        First_Pass.Value = "Failed1"
        First_Pass.BackColor = &HFF&
        First_Pass.ForeColor = &HFFFFFF
    End If
Else
    First_Pass.Value = "Failed2"
    First_Pass.BackColor = &HFF&
    First_Pass.ForeColor = &HFFFFFF
End If
If d = "" And PP_results.Value = "Untested" Then
        First_Pass.Value = "In Progress"
        First_Pass.BackColor = &H8000000F
        First_Pass.ForeColor = &HFF0000

End If

End Sub
Any suggestions as to what is causing the error

Hi.

This is a simple version of my spreadsheet:
******** ******************** ************************************************************************>Microsoft Excel - Book5.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutE19=
ABCDEF1StatusIDCustomerValuePaymentMailing2TRUE1Customer*1HighBACsall3TRUE2Customer*2LowBACsweekly4FALSE3Customer*3LowBACsmonthly5FALSE4Customer*4HighBACsall6TRUE5Customer*5LowBACsdaily7FALSE6Customer*6LowBACsallSheet1*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

The main query is about making this code easier. It works but it seems a very long winded way of changing the format of four cells between two states. Blue for False and Grey for True... Theres more details in the code

Code:
Private Sub CheckBox1_Click()
'
' When the checkbox is ticked, I want four of the cells in the row, those highlighted
' within the "variables" section to have there locked & hidden properties = true
' and their font colour changed to grey. 
' When unticked, I want the cells to be coloured blue and have conditional formating 
' on one of them and have the locked & hidden properties = false
'
' This does work and does what I need however I have over 100 check boxes on this sheet
' alone and I suspect that their is a better method that the one below. 
' For example, each checkbox requires that I change the variables each time. :0(
'
' The other thing I have tried to get this script to do is have no focus when the 
' checkbox is unticked.  At present it runs the script and ends with focus on cell04.
'
' Is there VB code to remove the focus until I click a cell again?
'
'
''
Set cb = CheckBox1
Set cell01 = Range("C14")
Set cell02 = Range("D14")
Set cell03 = Range("E14")
Set cell04 = Range("F14")
'------------------------'

' The checkbox is ticked
If cb.Value = True Then
ActiveSheet.Unprotect
cell01.Select
ActiveCell.Font.Color = 8421504
Selection.Locked = True
Selection.FormulaHidden = True
cell04.Select
ActiveCell.Font.Color = 8421504
Selection.Locked = True
Selection.FormulaHidden = True
cell03.Select
ActiveCell.Font.Color = 8421504
cell02.Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Delete
ActiveCell.Font.Color = 8421504
Selection.Locked = True
Selection.FormulaHidden = True
ActiveSheet.Protect

' The check box is unticked
Else
ActiveSheet.Unprotect
cell01.Select
ActiveCell.Font.Color = 16711680
Selection.Locked = False
Selection.FormulaHidden = False
cell04.Select
ActiveCell.Font.Color = 16711680
Selection.Locked = False
Selection.FormulaHidden = False
cell02.Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""high"""
Selection.FormatConditions(1).Font.ColorIndex = 10
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""low"""
Selection.FormatConditions(2).Font.ColorIndex = 3
Selection.Locked = False
Selection.FormulaHidden = False
cell03.Select
ActiveCell.Font.Color = 0
ActiveSheet.Protect
End If
End Sub

´Cheers for you help!


Ok, I need to make a scatter plot that graphs points on an XY plane based on 2 variables. The part Im having trouble with is that I would like the points to be different colors depending on a 3rd variable.

Simple example would be:

Chart points by Revenue and Number of Employees. Then the 3rd variable would be making the points green if theyre in the NE of the country, Red if their in the NW region of the country, etc.

Thanks in advance, Im new here, but this board has saved me many times already.

Hi I have 2 columns of symbols with 8 variations as follows

Col 1 Col 2
=

<

> =
> >

and I wish to output a different message for each scinario ie if C1 = "=" &
C2 = "

Hi, I'd like to set up a userform that allows you to toggle if a column is displayed on a new sheet or not.
I've got the code sorted (thanks to lots of help fromthis forum) to display the default columns but I need to change the code under Private Sub ExtraData to make this happeen.
At the moment I have to change the data manualy or duplicate it and add to each button.Ticking a box to decide which columns to use then pressing a button to display the sheet would be ideal. something along the lines of if pressing "show bakery" button would show some default columns and pressing "Edit bakery" button would bring up a userform with preset columns flagged but with the option to add more columns and then have those become the default if next time you just press the "Show Bakery" button. I hope this makes some sort of sense? Any help would be great Thank you.

Here's my code for the menu I use

Private Sub ExtraData()
Set wsM = Sheets("Main")
    Set wsM2 = Sheets("Main2")
    With wsM2
        wsM.Columns("bm:bm").Copy .Range("j1")
        wsM.Columns("bn:bn").Copy .Range("k1")
        wsM.Columns("bo:bo").Copy .Range("l1")
        wsM.Columns("bp:bp").Copy .Range("m1")
    End With
End Sub

Private Sub MainData()

    Dim wsM As Worksheet, wsM2 As Worksheet
    Dim LRow2            As String
    'sets the variables wsM equal to Main worksheet and wsM2 equal to Main2
    Set wsM = Sheets("Main")
    Set wsM2 = Sheets("Main2")
    LRow2 = 0
    'reset main
        Application.ScreenUpdating = False    ' turns off screen refreshing.
        Sheets("main").Select
If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
    'select the Main2 worksheet
    With wsM2
    wsM2.UsedRange.Clear
        'select all the cells
        wsM.UsedRange.AutoFilter Field:=3, Criteria1:="Bakery"
        wsM.Columns("b:b").Copy .Range("a1")
        wsM.Columns("d:d").Copy .Range("b1")
        wsM.Columns("f:f").Copy .Range("c1")
        wsM.Columns("q:q").Copy .Range("e1")
        wsM.Columns("r:r").Copy .Range("g1")
        wsM.Columns("g:g").Copy .Range("h1")
        
        'add on other columns
        Call ExtraData
        
'sets variable LRow2 equal to the last row in column A of the
    'activesheet that contains data
    LRow2 = wsM2.Cells(wsM2.Rows.Count, "A").End(xlUp).Row
        
' Set D column Due for Think 25
        'put a value in column D cell D1
        .Range("D1").Value = "Due"
        'value in D2 equal to the formula
        'copy the formula
        .Range("D2:D" & LRow2).Formula = "=c2-(TODAY()-182)"
        'set conditional formating
        With .Range("D2:D" & LRow2)
            .FormatConditions.Delete
            .Range("d2,d" & LRow2).NumberFormat = "0"
            .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
                                  Formula1:="30"
            .FormatConditions(1).Font.ColorIndex = 15
            .FormatConditions(1).Interior.ColorIndex = 2
            .FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
                                  Formula1:="1", Formula2:="30"
            .FormatConditions(2).Font.ColorIndex = 2
            .FormatConditions(2).Interior.ColorIndex = 37
            .FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
                                  Formula1:="=TODAY()-182"
            .FormatConditions(3).Font.ColorIndex = 2
            .FormatConditions(3).Interior.ColorIndex = 9
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
        End With

' Set F Column Due for Fire
 'put a value in column D cell D1
        .Range("f1").Value = "Due"
        'value in f2 equal to the formula
        'copy the formula
        .Range("f2:f" & LRow2).Formula = "=e2-(TODAY()-365)"
        'set conditional formating
        With .Range("f2:f" & LRow2)
            .FormatConditions.Delete
            .Range("f2,f" & LRow2).NumberFormat = "0"
            .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
                                  Formula1:="30"
            .FormatConditions(1).Font.ColorIndex = 15
            .FormatConditions(1).Interior.ColorIndex = 2
            .FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
                                  Formula1:="1", Formula2:="30"
            .FormatConditions(2).Font.ColorIndex = 2
            .FormatConditions(2).Interior.ColorIndex = 37
            .FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
                                  Formula1:="=TODAY()-365"
            .FormatConditions(3).Font.ColorIndex = 2
            .FormatConditions(3).Interior.ColorIndex = 9
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
        End With

' Set I Column Due for CPrs
 'put a value in column D cell D1
        .Range("i1").Value = "Due"
        'value in i2 equal to the formula
        'copy the formula
        .Range("i2:i" & LRow2).Formula = "=h2-(TODAY()-182)"
        'set conditional formating
        With .Range("i2:i" & LRow2)
            .FormatConditions.Delete
            .Range("i2,i" & LRow2).NumberFormat = "0"
            .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
                                  Formula1:="30"
            .FormatConditions(1).Font.ColorIndex = 15
            .FormatConditions(1).Interior.ColorIndex = 2
            .FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
                                  Formula1:="1", Formula2:="30"
            .FormatConditions(2).Font.ColorIndex = 2
            .FormatConditions(2).Interior.ColorIndex = 37
            .FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
                                  Formula1:="=TODAY()-182"
            .FormatConditions(3).Font.ColorIndex = 2
            .FormatConditions(3).Interior.ColorIndex = 9
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
        End With
        'set widths
        .Columns("A:A").ColumnWidth = 15
        .Columns("B:B").ColumnWidth = 12
        .Range("C:C,E:E").ColumnWidth = 7.5
        .Columns("D:D").ColumnWidth = 4
        .Columns("F:F").ColumnWidth = 4
        .Range("G:G,H:H").ColumnWidth = 7.5
        .Columns("I:I").ColumnWidth = 4
        .Range("J:J,K:K,L:L,M:M").ColumnWidth = 7.5
        '.Columns("F:F").EntireColumn.AutoFit
    End With
    If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False
        Application.ScreenUpdating = True    ' turns back on screen refreshing.
    Sheets("main2").Select
End Sub


Private Sub CB_Bakery_Click()
Call MainData
End Sub


I have a program that is mostly working but I have discovered, more or less
by trial and error that if my cursor is not on the row in which the program
will start adding data, then the conditional format formula my procedure
builds will use the incorrect row. I know that the variable I am using
represents the correct row for the CF as I have put a break point on that
line, but still the CF does not use the variables content unless I make sure
my cursor is on the row that the variable is equal to.
Here is the line where I have my breakpoint and below that is the complete
section where the problem is occurring. It doesn't seem like I should have
to have my cursor in any particular row, and if anyone else ever ends up
using this, I know that is going to be too hard to explain why they need to
do that.

..FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND($A" & lTrackStartRow & ",$C" & lTrackStartRow & ")"

Say I start out on row 7 and the procedure is bringing in 4 rows worth of
data from another spreadsheet. When I run this the first time,
ltrackstartrow = 7. CF that is created is perfect. I want to test it again,
so I run the macro again (when I first noticed the problem) without moving my
cursor. When I debug the program during the 2nd run, ltrackstartrow is 11
which is correct, but when I look at the CF, it is referencing row 15. While
experimenting, I had my cursor on, for instance row 4 before running it the
first time, and my CF was wrong. And finally, just a while ago, I ran the
procedure 3 times in a row, each time making sure my cursor was in the row
that would be the same as ltrackstartrow. Every time the CF that was built
was correct.

I hope someone can offer a solution so that the CF will be correctly built
regardless of where my cursor is when I run this. If you need to see the
entire procedure, let me know and I will post it.
Thanks.

With wsTracking
.Range(.Cells(lTrackStartRow, iTrackStartCol),
..Cells(lTrackFinalRow, 21)).Style = "MyInput"
With .Range(.Cells(lTrackStartRow, iTrackStartCol + 12), _
.Cells(lTrackFinalRow, iTrackStartCol + 15))
.FormulaR1C1 = "=if(weekday(rc[-1])>3,rc[-1]+5,rc[-1]+3)"
.Style = "MyFormula"
.NumberFormat = "m/d/yyyy"
End With
' The above takes care of everything except 2 columns within the
range that
' need a slightly different formula
With .Range(.Cells(lTrackStartRow, iTrackStartCol + 12), _
.Cells(lTrackFinalRow, iTrackStartCol + 12))
.FormulaR1C1 = "=if(weekday(rc[-1])=6,rc[-1]+3,rc[-1]+1)"
End With
With .Range(.Cells(lTrackStartRow, iTrackStartCol + 14), _
.Cells(lTrackFinalRow, iTrackStartCol + 14))
.FormulaR1C1 = "=if(weekday(rc[-1])=6,rc[-1]+3,rc[-1]+1)"
End With
With .Range(.Cells(lTrackStartRow, iTrackStartCol), _
.Cells(lTrackFinalRow, iTrackStartCol + 8))
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND($A" & lTrackStartRow & ",$C" & lTrackStartRow & ")"
.FormatConditions(1).Interior.ColorIndex = 46
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND($A" & lTrackStartRow & ",$B" & lTrackStartRow & ")"
.FormatConditions(2).Interior.ColorIndex = 6
End With
With .Range(.Cells(lTrackStartRow, iTrackStartCol + 12), _
.Cells(lTrackFinalRow, iTrackStartCol + 12))
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND($A" & lTrackStartRow & ",not($C" _
& lTrackStartRow & "),$b" & lTrackStartRow & ")"
.FormatConditions(1).Interior.ColorIndex = 6
End With
With .Range(.Cells(lTrackStartRow, iTrackStartCol + 14), _
.Cells(lTrackFinalRow, iTrackStartCol + 14))
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND($A" & lTrackStartRow & ",$C" & lTrackStartRow & ")"
.FormatConditions(1).Interior.ColorIndex = 46
End With
End With
--
Kevin Vaughn

I have a complex spread sheet and I'm trying to format a cell based on the data provided from the cell next to it there are 3 columns to take into account in this. Ill give you an example then explain what I'm trying to achieve.

00|___J___|___K___|___L___|
01|_______|_______|_______|
02|_______|_______|_______|
03|__70___|_ Input _| answer |
04|__90___|_ Input _| answer |
05|__45___|_ Input _| answer |
ect.
ect.
55|__35___|_ Input _| answer |
56|_______|_______|_______|

Content starts on Cell 3
Content ends on Cell 55

The Answer in Column " L" is as follows, =IF((J3>K3), "Buy", "Make") so it will always display Buy or Make regardless of the input blank or not.
Column K is an input in the form of a number. The Idea is Column J is a variable number and if the users number In K is less than the variable in J , L will display Buy , other wise Make.

The problem is , the column K needs to be blank until the user has Input there numbers so column L needs to be hidden probably with formatting to keep a neat non confusing appearance. ( like black on black text and background).

But after data is input into K and runs threw the check of greater than less than i want them to view the answer in column L. So a working set of cells would look like this.

00|___J___|___K___|___L___|
01|_______|_______|_______|
02|_______|_______|_______|
03|__70___|_______|||||||||||||||||
04|__50___|___35__|__Buy__|
05|__65___|___90__|_Make__|
06|__47___|_______|||||||||||||||||
ect.

Now i can make it all work with ISBLANK and LEN or what not, bot thats only works for a 2 cell comparison, IE , =LEN(K3)>0 ..... with the active cell being L3. This translates to me having to manually type out this string of code in and with the corresponding cells resulting in 50 + Conditional Formats , a hand full is one thing but if the sheet grows i will never be able to keep up. Is there any ways to format Column L off of the adjacent cell in column K infinity down or fixed to a set of cells?

Thanks Matt

Hi,
I was given some vba code that checks one range of cells against a second range for a match, and conditionally format any cells in the first range the same background color as the cell in the second range.

A third cell is used to hold a variable value (ie. 1, 2, or 3, etc) that determines how far apart (+/-) the match between the two values can be.

The code partially worked in the initial sample workbook, but when I placed it in the actual workbook and changed the target cells, it wouldn’t work at all. (see attatched files)

Can someone please tell me what's wrong with it?

Thanks a lot

Hi I am trying to create a spreadsheet with many variables, and want them all to conditionally format when they auto-populate various other worksheets. The auto-formatting will vary from sheet to sheet dependant on primary user preferences, but the template will be the same for all. I am having issues with the template though, as I can only get the first 3 commands to run on it? Can anyone out there fix it as I am a total newb on VBA, and have taught myself everything I know on it in the last 2 days...

Any help would be much appreiciated,

Cheers,

oli

Here is the macro:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 09/06/2011 by SMITHSO
'

'
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="=""A00""", Formula2:="=""A99"""
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = False
        .ColorIndex = 2
    End With
    Selection.FormatConditions(1).Interior.ColorIndex = 45
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="=""B00""", Formula2:="=""B99"""
    With Selection.FormatConditions(2).Font
        .Bold = True
        .Italic = False
        .ColorIndex = 2
    End With
    Selection.FormatConditions(2).Interior.ColorIndex = 13
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=""FIELD1"""
    With Selection.FormatConditions(3).Font
        .Bold = True
        .Italic = False
        .ColorIndex = 2
    End With
    Selection.FormatConditions(3).Interior.ColorIndex = 12
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=""FIELD2"""
    With Selection.FormatConditions(4).Font
        .Bold = True
        .Italic = False
        .ColorIndex = 2
    End With
    Selection.FormatConditions(4).Interior.ColorIndex = 5
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=""FIELD3"""
    With Selection.FormatConditions(5).Font
        .Bold = True
        .Italic = False
        .ColorIndex = xlAutomatic
    End With
    Selection.FormatConditions(5).Interior.ColorIndex = 37
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=""FIELD4"""
    With Selection.FormatConditions(6).Font
        .Bold = True
        .Italic = False
        .ColorIndex = 6
    End With
    Selection.FormatConditions(6).Interior.ColorIndex = 41
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=""FIELD5"""
    With Selection.FormatConditions(7).Font
        .Bold = True
        .Italic = False
        .ColorIndex = 10
    End With
    Selection.FormatConditions(7).Interior.ColorIndex = 3
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=""FIELD6"""
    With Selection.FormatConditions(8).Font
        .Bold = True
        .Italic = False
        .ColorIndex = 2
    End With
    Selection.FormatConditions(8).Interior.ColorIndex = 1
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=""FIELD7"""
    With Selection.FormatConditions(9).Font
        .Bold = True
        .Italic = False
        .ColorIndex = 3
    End With
    Selection.FormatConditions(9).Interior.ColorIndex = 35
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=""FIELD8"""
    With Selection.FormatConditions(10).Font
        .Bold = True
        .Italic = False
        .ColorIndex = 2
    End With
    Selection.FormatConditions(10).Interior.ColorIndex = 3
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=""FIELD9"""
    With Selection.FormatConditions(11).Font
        .Bold = True
        .Italic = False
        .ColorIndex = 2
    End With
    Selection.FormatConditions(11).Interior.ColorIndex = 7
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=""FIELD10"""
    With Selection.FormatConditions(12).Font
        .Bold = True
        .Italic = False
        .ColorIndex = xlAutomatic
    End With
    Selection.FormatConditions(12).Interior.ColorIndex = 38
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=""FIELD11"""
    With Selection.FormatConditions(13).Font
        .Bold = True
        .Italic = False
        .ColorIndex = 4
    End With
    Selection.FormatConditions(13).Interior.ColorIndex = 1
End Sub


Dear Excel-ers,

I am new to this forum and completely new to Excel macros so I hope someone can advise me!

I have a worksheet consisting of 85 columns of reaction time data, each column representing a variable or condition.

I wish to highlight outliers - in this case, those that are more than 2.5 standard deviations away from the column mean.

So, under each column I've calculated the mean, standard deviation, and standard deviation x 2.5.

To highlight outliers in the first column I did the following:

1) selected the range of cases (a2 - a40)
2) applied conditional formatting (red type) if values were greater than the mean + sd*2.5 or less than the mean - sd*2.5 (the mean and sd*2.5 values are taken from the previously calculated values at the bottom of each column.

Which did the trick. However, as I have 85 columns and then over 30 workbooks with an identical format, it would be great to automate this process.

I've never used macros before in Excel, but I tried recording a macro while completing the above actions for a few columns:

    Range("A2:A40").Select
    Application.CutCopyMode = False
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="=$A$45+$A$50"
    Selection.FormatConditions(1).Font.ColorIndex = 3
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
        Formula1:="=$A$45-$A$50"
    Selection.FormatConditions(2).Font.ColorIndex = 3
    Range("B2:B40").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="=$B$45+$B$50"
    Selection.FormatConditions(1).Font.ColorIndex = 3
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
        Formula1:="=$B$45-$B$50"
    Selection.FormatConditions(2).Font.ColorIndex = 3
    Range("C2:C40").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="=$C$45+$C$50"
    Selection.FormatConditions(1).Font.ColorIndex = 3
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
        Formula1:="=$C$45-$C$50"
    Selection.FormatConditions(2).Font.ColorIndex = 3
End Sub
So, my question is, other than manually recording a macro and repeating this action for the entire 85 columns, or manually editing the code to apply to the different columns, is there a simpler way to achieve this? The range of cells I want to highlight is the same in each column. Also the position of the mean, sd and sd*2 is the same in each column. I rather suspect that they wouldn't be necessary at all to someone with a modicum of macro experience.

Any suggestions would be greatly appreciated. Many thanks for reading and do let me know if any further information is required.


No luck finding an answer? You could always try Google.