CODEWITHSUNDEEP
excelexcel-formula
NSN
NSN

Reputation: 750

excel formula suggestion required

Based on the -2 or +2 entry, we would like to paint range with yellow. Need your suggestions to fix it.

Question updated:

Sorry for poor explanation. I have column A, B, C. if I enter -2 in B10, A10,A9,A8 will be colored. If I enter +2 in C10, A10,A11, A12 will be colored. The value I can enter in B is -10 to -1 and C is 1 to 10. So the formula that i can use in conditional format should be more generic.

enter image description here

Upvotes: 0

Views: 94

Answers (2)

ian0411
ian0411

Reputation: 4265

Revised. Now I think I got what you want.

According to your description, here is what you can do:

  • To avoid the end users enter any invalid number in column B or C, use Data > Data Validation. See the attached pic below for example on how to setup rules for column B. Please adjust the criteria accordingly to fit your need.

enter image description here

P.S. This pic below just to show you when an end user entered a value that is out of the specified range, it would show an error message.

enter image description here

  • To display the color coded cell depends on the value that you enter in cell B10 or cell C10, select Home > Conditional Formatting > New Rule > Use a formula.... I used this formula =ROW($A1)-10>=$B$10, and applied to =$A$1:$A$10. Then =ROW($A10)-10<=$C$10, and applied to =$A$10:$A$20 as you can see two pics below.

enter image description here

enter image description here

This is to determine the row position and the value that the user entered. You may want to change that -10 to ROW($B$10) or ROW($C$10) for dynamic purpose but that is up to you.

Please note that cell A10 got referenced twice on the conditional formatting. Whichever rule goes first will show that color and you can change the order by clicking the arrow sign that shows on pic#4.

Please try again and let me know if this works or not.

Upvotes: 1

Apurv Pawar
Apurv Pawar

Reputation: 424

this might be useful for you.

Sub yellow()

    Dim i As Integer

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

    For k = 2 To lastrow
    If Cells(k, 1).Value = 2 Or Cells(k, 1).Value = -2 Then
    Cells(k, 1).Interior.ColorIndex = 6
    End If
    Next
    End Sub

Upvotes: 2

Related Questions