Dim text on other cell value

[apgmin]

I have in Column A, list of questions, In column B the students fill the answers, adjacent to that question. Say the first question is in Cell A1 and the answer is to be put in Cell B1

What should I do to make question in all other cells in column A invisible except the question in Cell A2 , till the value is put in Cell B1

Similarly, What should I do to make question in all other cells in column A invisible except the question in Cell A1:A3 , till the value is put in Cell B2

 

[SeniorNewbie]

What a funny idea! But as a former teacher I guess I may know what you mean
Actually the're two ways to solve this problem.

Method one: Use conditional formatting and make the cells you don't want to show text and background the same color. But that's a mess of formulas
Method two needs VBA and a second sheet: With the sheets event Selection Change check the last row if the're answers in column B. If yes copy the next question line from sheet B which you should set to xlVeryHidden.

Your code for sheet A

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Integer, r As Integer

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

    If Cells(2, r) <> "" Then
        Cells(r + 1, 1) = ThisWorkbook.Sheets(2).Cells(r + 1, 1)
    End If
End Sub

Have fun!

 

[SeniorNewbie]

Sorry the code above doesn't work. Have to fine tune

 

[RoryA]

I'd use conditional formatting.
Select the answers from A2 (not A1) down. Apply conditional formatting based on a formula (Home tab, Conditional Formatting, New rule) and use the formula:
=B1=""
Note that there are no $ signs in there.

Click Format..., then select the Number tab and specify a custom number format of:
:::

and OK out

 

[SeniorNewbie]

Better use this one:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim r As Integer

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

    If Cells(r, 2) <> "" Then
        Cells(r + 1, 1) = ThisWorkbook.Sheets(2).Cells(r + 1, 1)
    End If

End Sub

Take care so have the source (question) in the same row you want to show it in examination sheet.

Have fun!