Excel countIf + concatenate function

Question

I have an excel formula that does a simple count if. However, I want to add a condition to that, if met would change the fill colour of the cell. Example: In Sheet 1 cell A1 I have "XXX". In Sheet 1 cell A2 I have a boolean of "True". In Sheet 2 cell B1, I have

    =COUNTIF(A1,"*XXX*")

What I want to do is say that if in Sheet 1 cell A1 I have XXX AND Sheet 1 Cell A2 is "True" then in Sheet 2 cell B1 I should have "1" displayed within a red cell

An alternate to the color coding would be to say that if Sheet 1 cell A2 has a boolean of "True" and Sheet 1 cell A1 has XXX then on sheet 2 cell B1, I should have "1R" displayed.

Any help is appreciated

Answer 1

If you want to do it with VBA, this code will do what you want:

Option Explicit
Sub test()

Dim wb As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
Dim Lastrow As Long
Dim i As Long

Set wb = ThisWorkbook
Set ws1 = wb.Sheets("Sheet1") 'Change the name of your sheet
Set ws2 = wb.Sheets("Sheet2") 'Change the name of your sheet

Lastrow = ws1.Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To Lastrow Step 2

    If ws1.Cells(i, "A") = "SEC" And ws1.Cells(i + 1, "A") = True Then
        ws2.Cells(i, "B") = 1
        ws2.Cells(i, "B").Interior.Color = RGB(255, 0, 0)
'       ws2.Cells(i, "B") = "1R" ' Alternate to the color coding
    End If

Next i

End Sub

EDIT:

Code based on commnents below

Option Explicit
Sub test()

Dim wb As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
Dim Lastrow As Long
Dim i As Long, c As Long

Set wb = ThisWorkbook
Set ws1 = wb.Sheets("Sheet1") 'Change the name of your sheet
Set ws2 = wb.Sheets("Sheet2") 'Change the name of your sheet

i = 3
    For c = 7 To 25


        If ws2.Cells(i, "K") > 0 And ws2.Cells(i, "L") = False Then
            ws1.Cells(3, c).Interior.Color = RGB(255, 0, 0)

        ElseIf ws2.Cells(i, "K") > 0 And ws2.Cells(i, "L") = True Then
            ws1.Cells(3, c).Interior.Color = RGB(0, 255, 0)
        End If
i = i + 2

Next c


End Sub

Answer 2

I think the easiest way is to use Conditional formatting, which you will find at Start>Styles.

1. Select the cell that you need the colour to change
2. Add a new Rule
3. Make it formula dependant
4. Use the formula =(Sheet1!A2=TRUE)
5. Format the sample cell below to match your requirements
6. Add a new rule for each color you want (in case you prefered green for FALSE items, for example)

Answer 3

Alternatively you could say:=if(and(a1="sec",a2="true"),1,"False Text") This will only return 1, or you could amend to "1R", if both a1 and A2 return the test you put into the and statement.