Excel Missing text formula

[slypie]

Hi all, wondering if anyone can help me. I need to create a spreadsheet that people import a password log into. I've created a macro to do this but then need to create a formula to say that if text is in column A (this will be the username) but is missing out of column B (the password) then it needs to put a colour in Column B so it's easy to spot the password is missing.

Any help would be really appreciated...

[Domski]

Hi,

It would be easiest to see the code that you are using.

Dom

[slypie]

Hi,

It would be easiest to see the code that you are using.

Dom

Dom, I'm not using any code at all as I'm totally stuck. It took me a day to work out the Macro. I've tried conditional formatting but it highlights all blank cells in the worksheet which I don't want. All the formulas I've googled don't match up with what I'm trying to do. Excel is like the blackarts to me tbh.

[Domski]

I've created a macro to do this

I assumed you had some code already.

If you don't have anything can you explain step by step how you expect the sheet to be completed, at what point you want the warning to appear etc

Dom

[Domski]

Thinking about it you should be able to do it using conditional formatting...

Select Column B and the in conditional formatting change the drop down to Formula Is and use the formula =AND(A1<>"",B1="") and then set your format.

Dom

[Palmetto]

Change the colorindex value per your requirements (set to yellow)

Option Explicit

Sub Color_cells_Missing_Text()

    Dim lrow As Long

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

    Range("B1:B" & lrow).FormatConditions.Add Type:=xlExpression, Formula1:="=AND(LEN($A1)>0,ISBLANK($B1))"
    Range("B1:B" & lrow).FormatConditions(1).Interior.ColorIndex = 6

End Sub

[slypie]

Cheers for the help guys, have some rep Excel isn't my strongpoint to say the least.