-1

I would like to have the column A from A6 down to change font to bold if any data on its row (from b6 onwards) is having FAIL value. Then I would like to have a cell that shows the count of the cell having the bold font. Below is so far what i have.

Sub Check_Results()
'
' Check_Results Macro
'
' Keyboard Shortcut: Ctrl+Shift+C
'
    Sheets("Macro (2)").Select
    Sheets("Macro (2)").Copy Before:=Sheets(1)
    Sheets("Macro (2)").Select
    Cells.Select
    Selection.Copy
    Sheets("Macro (3)").Select
    Cells.Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Sheets("Macro (2)").Select
    Cells.Select
    Selection.Copy
    Sheets("Macro (3)").Select
    Cells.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B20").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=+IF(('Macro (2)'!R5C)>0,IF(ABS('Macro (2)'!RC)>400%,IF(AND(ABS(PRE!RC)<100E-9,ABS(POST!RC)<100E-9),""pass"",""FAIL""),""pass""),IF(ABS('Macro (2)'!RC)>20%,""FAIL"",""pass""))"
    Range("B20").Select
    Selection.Copy
    Range(Selection, Selection.End(xlToRight)).Select
    ActiveSheet.Paste
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Range("A1").Select
    Sheets("Macro (2)").Select
    Range("A1").Select

       Sheets("Macro (3)").Select
    Range("A1").Select
        Cells.Select
    Range("H15").Activate
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=""pass"""
    Selection.FormatConditions(1).Interior.ColorIndex = 35
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=""FAIL"""
    Selection.FormatConditions(2).Interior.ColorIndex = 3
        Range("A1").Select
End Sub
Community
  • 1
  • 1
Elmer
  • 1
  • 1
  • 1
    *Welcome to [so]!* I'd suggest you first check out the [tour] (and earn a badge doing so!) as well as [ask] and also [mcve]. Then it would be good to clean up the code instead of just pasting in a recording. See [How to avoid using Select in Excel VBA](https://stackoverflow.com/q/10714251/8112776). After that it would be helpful if you added more detail about what you need to do, what you tried, and what went wrong. – ashleedawg Jun 23 '18 at 15:55
  • Is there a reason you are attempting this with a macro rather than using `countif()` and conditional formatting? – Jason Aller Jun 23 '18 at 18:44
  • I'm doing this in macro so it will be automatic. I will be using the template on day to day basis. and need to extract data on 84 rows each time. Anyway, I was able to incorporate the countif & conditional formatting in the code. – Elmer Jun 24 '18 at 19:49

1 Answers1

0

I was able to make it work i added the code below at the bottom.

Range("A1").Select
Columns("B:B").Insert Shift:=xlToRight
Range("A19").Select
Selection.FormatConditions.Delete
ActiveCell.FormulaR1C1 = "Fail"
Range("B20").Select
ActiveCell.FormulaR1C1 = _
   "=IF(COUNTIF(RC[+1]:RC[+51],R19C1),""F"","""")"
Range("B20").Select
Selection.AutoFill Destination:=Range("B20:B6000")
Range("B20:B6000").Select
ActiveCell.FormulaR1C1 = _
   "=IF(COUNTIF(RC[+1]:RC[+51],R19C1),""F"","""")"
Range("B19").Select
ActiveCell.FormulaR1C1 = "=countif(R20C2:R6000C2,""F"")"
Range("B1").EntireColumn.AutoFit
Elmer
  • 1
  • 1